-1

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?

Community
  • 1
  • 1
Manas Saxena
  • 2,171
  • 6
  • 39
  • 58
  • I'm not sure there is a completely generic way [apart from this](http://stackoverflow.com/q/9026184/266304). Oracle has `exists` from you have to select from dual, which isn't generic. Many similar questions have been asked before though... – Alex Poole Aug 04 '16 at 08:39

2 Answers2

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

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.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260