What's the Best to Check If Item Exist or Not: Select Count(Id)Or Exist(...)

What's the best to check if item exist or not: Select Count(ID)OR Exist(...)?

EXISTS, always

• COUNT will traverse the table or an index: you asked for a COUNT
• EXISTS will stop as soon as it finds a row

Edit, to be clear

Of course, in this case if the email column is unique and indexed it will be close.

Generally, EXISTS will use less resources and is more correct too. You are looking for existence of a row, not "more than zero" even if they are the same

Edit2: In the EXISTS, you can use NULL, 1, ID, or even 1/0: it isn't checked...

21 May 2011 edit:

It looks like this was optimised in SQL Server 2005+ so COUNT is now the same as EXISTS in this case

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

SQL: How to properly check if a record exists

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

What is the best way to check if a record exists in a SQL Server table using C#?

Exists is more efficient than Count, because count needs to scan all rows to match the criteria and include in the count, exist dont.

So exists with ExecuteScalar is better.

As more info backing this:

According to http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

Both queries scanned the table but the EXISTS was able to at least do
a partial scan do to the fact it can stop after it finds the very
first matching row. Where as the COUNT() must read each and every row
in the entire table to determine if they match the criteria and how
many there are. That is the key folks. The ability to stop working
after the first row that meets the criteria of the WHERE clause is
what makes EXISTS so efficient. The optimizer knows of this behavior
and can factor that in as well. Now keep in mind that these tables are
relatively small compared to most databases in the real world. So the
figures of the COUNT() queries would be multiplied many times on
larger tables. You could easily get hundred's of thousands of reads or
more on tables with millions of rows but the EXISTS will still only
have just a few reads on any queries that can use an index to satisfy
the WHERE clause.

As a simple experiment using AdventureWorks with MSSQL 2012

set showplan_all on

-- TotalSubtreeCost: 0.06216168
select count(*) from sales.Customer

-- TotalSubtreeCost: 0.003288537
select 1 where exists (select * from sales.Customer)

See also

http://sqlmag.com/t-sql/exists-vs-count

UPDATE: On ExecuteScalar vs ExecuteReader.
Having a look with a disassembler (like Reflector) on the Implementation of System.Data.SqlClient.SqlCommand methods, shows something surprising, they are kind of equivalent: both end up calling the internal helper
internal SqlDataReader RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, string method, TaskCompletionSource completion, int timeout, out Task task, bool asyncWrite = false)

which returns a SqlDataReader, the ExecuteReader returns it as is.
While ExecuteScalar consumes it with another helper:

private object CompleteExecuteScalar(SqlDataReader ds, bool returnSqlValue)
{
    object obj2 = null;
    try
    {
        if (!ds.Read() || (ds.FieldCount <= 0))
        {
            return obj2;
        }
        if (returnSqlValue)
        {
            return ds.GetSqlValue(0);
        }
        obj2 = ds.GetValue(0);
    }
    finally
    {
        ds.Close();
    }
    return obj2;
}

As a side note, same goes with MySQL Connector/NET (The official ADO.NET open source driver for MySQL), the method ExecuteScalar internally creates an DataReader (MySqlDataReader to be more precise) and consumes it. See on source file /Src/Command.cs (from https://dev.mysql.com/downloads/connector/net/ or https://github.com/mysql/mysql-connector-net).

Summary: Regarding the ExecuteScalar vs ExecuteReader both incurr in the overhead of creating a SqlDataReader, I would say the difference is mostly idiomatic.

COUNT vs SELECT in SQL

The idea should be to that we only need to find one record in order to say that such record exists. This can be done with an EXISTS clause in standard SQL.

select exists (select * from mytable where name = 'searchedName');

returns true if the table contains a record with 'searchedName' and false otherwise.

If you want 0 for false and 1 for true instead (e.g. if the DBMS does not support booleans):

select case when exists (select * from mytable where name = 'searchedName') 
  then 1 else 0 end as does_exist;

You say you want this for Oracle. In Oracle you can use above query, but you'd have to select from the table dual:

select case when exists (select * from mytable where name = 'searchedName') 
  then 1 else 0 end as does_exist
from dual;

But for Oracle we'd usually use rownum instead:

select count(*) as does_exist
from mytable 
where name = 'searchedName'
and rownum = 1; -- to find one record suffices and we'd stop then

This also returns 1 if the table contains a record with 'searchedName' and 0 otherwise. This is a very typical way in Oracle to limit lookups and the query is very readable (in my opinion).

Is EXISTS more efficient than COUNT(*) 0?

Yes, MySQL (indeed all database systems as far as I'm aware) will stop processing when a row is returned when using an Exists function.

You can read more at the MySQL documentation:
If a subquery returns any rows at all, EXISTS subquery is TRUE.

So what IS the best way to check if a row exists? EXISTS, COUNT or num_rows?

Option 3 is the fastest way to check if a row exists if you are using MySQL:

$query = mysql_query("SELECT EXISTS(SELECT 1 FROM users WHERE id = 1)")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

---

Related Topics