2

I'm trying to find the most efficient way to determine if a table row exists.

I have in mind 3 options:

  1. SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);

  2. SELECT 1 FROM table1 WHERE some_condition LIMIT 0,1;

  3. SELECT COUNT(1) FROM table1 WHERE some_condition;

It seems that for MySQL the first approach is more efficient: Best way to test if a row exists in a MySQL table

Is it true in general for any database?

UPDATE:

I've added a third option.

UPDATE2:

Let's assume the database products are mysql, oracle and sql-server.

Community
  • 1
  • 1
dcernahoschi
  • 14,968
  • 5
  • 37
  • 59
  • What do you mean by "any database" exactly - any database product? – Pekka Jan 26 '12 at 22:14
  • I don't think that is possible to answer - each product's implementation will differ *massively*. You'll have to test with every product you're planning on using – Pekka Jan 26 '12 at 22:21

5 Answers5

4

I would do 

SELECT COUNT(1) FROM table 1 WHERE some_condition.

But I don't think it makes a significant difference unless you call it a lot (in which case, I'd probably use a different strategy).

Rajesh Patel
  • 1,946
  • 16
  • 20
Luis
  • 1,294
  • 7
  • 9
4

If you mean to use as a test if AT LEAST ONE row exists with some condition (1 or 0, true or false), then:

select count(1) from my_table where ... and rownum < 2;

Oracle can stop counting after it gets a hit.

tbone
  • 15,107
  • 3
  • 33
  • 40
3

Exists is faster because it will return the number of results that match the subquery and not the whole result.

Mike
  • 3,017
  • 1
  • 34
  • 47
3

The different methods have different pros and cons:

SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);

might be the fastest on MySQL, but

SELECT COUNT(1) FROM table 1 WHERE some_condition

as in @Luis answer gives you the count.

More to the point I recommend you take a look at your business logic: Very seldom is it necessary to just see if a row exists, more often you will want to

  • either use these rows, so just do the select and handle the 0-rows case
  • or you will want to change these rows, in which case just do your update and check mysql_affected_rows()
  • If you want to INSERT a row if it doesn't already exist, take a look at INSERT .. ON DUPLICATE KEY or REPLACE INTO
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
1

The exists function is defined generally in SQL, it isn't only as a MySQL function : http://www.techonthenet.com/sql/exists.php and I usually use this function to test if a particular row exists.

However in Oracle I've seen many times the other approach suggested before:

SELECT COUNT(1) FROM table 1 WHERE some_condition.
thermz
  • 2,386
  • 3
  • 20
  • 28