What is the fastest way to check if a record exists in db which will be applicable to oracle , mysql and postgres.
As per this link : Fastest way to determine if record exists , we can use the top clause but top is not available in oracle . I dont want to have different queries for different dbs.
Hence , what would be the best generic query to check if record exists in db?
There is probably no fast generic way because the SQL dialects are too different. One that will always work is
SELECT min(field) FROM mytable WHERE field = ?;
If the database is very smart (not tested if any is), it might figure out that it can stop after the first record found; but if not count(*) might be faster.
Another idea is to use a cursor in your programming language and only fetch 1 row. Smart databases (like PostgreSQL) will figure out that they should choose a plan that gets the first few rows quickly.
In Postgresql:
select exists (select 1 from t where column = value);
Generic using limit:
select 1
from t
where column = value
limit 1
Using select 1 (or any constant) in instead of select * will allow an index only search if the database has that capability.
limit isn't generic - Oracle doesn't support that (even in 12c, the syntax is different).
existsfrom you have to select from dual, which isn't generic. Many similar questions have been asked before though...