an efficient way to test if a table row exists

Question

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.

Solution 1

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).

Solution 2

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.

Solution 3

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

Solution 4

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

Solution 5

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.

View more solutions