SQL How to properly check if a record exists

Question

While reading some SQL Tuning-related documentation  I found this   SELECT COUNT        Counts the number of rows  Often is improperly used to verify the existence of a record    Is SELECT COUNT    really that bad     What s the proper way to verify the existence of a record

User · Answer

You can use   SELECT COUNT 1  FROM MyTable WHERE        or  WHERE  NOT  EXISTS    SELECT 1 FROM MyTable WHERE         This will be more efficient than SELECT   since you re simply selecting the value 1 for each row  rather than all the fields   There s also a subtle difference between COUNT    and COUNT column name     COUNT    will count all rows  including nulls COUNT column name  will only count non null occurrences of column name

User · Answer

I m using this way   IIF EXISTS  SELECT TOP 1 1                  FROM Users                  WHERE FirstName    John    1  0  AS DoesJohnExist

User · Answer

Other option   SELECT CASE     WHEN EXISTS           SELECT 1         FROM  MyTable  AS  MyRecord       THEN CAST 1 AS BIT  ELSE CAST 0 AS BIT  END

User · Answer

You can use   SELECT 1 FROM MyTable WHERE  lt MyCondition gt    If there is no record matching the condition  the resulted recordset is empty

User · Answer

SELECT COUNT 1  FROM MyTable WHERE       will loop thru all the records   This is the reason it is bad to use for record existence   I would use  SELECT TOP 1   FROM MyTable WHERE       After finding 1 record  it will terminate the loop

User · Answer

I would prefer not use Count function at all   IF  NOT  EXISTS   SELECT 1 FROM MyTable WHERE             lt do smth gt      For example if you want to check if user exists before inserting it into the database the query can look like this   IF NOT EXISTS   SELECT 1 FROM Users WHERE FirstName    John  AND LastName    Smith    BEGIN     INSERT INTO Users  FirstName  LastName  VALUES   John    Smith   END

User · Answer

You can use   SELECT 1 FROM MyTable WHERE    LIMIT 1   Use select 1 to prevent the checking of unnecessary fields   Use LIMIT 1  to prevent the checking of unnecessary rows

User · Answer

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

User · Answer

The other answers are quite good  but it would also be useful to add LIMIT 1  or the equivalent  to prevent the checking of unnecessary rows

[sql] SQL: How to properly check if a record exists

Examples related to sql

Examples related to optimization

Examples related to query-optimization