Sql: How to properly check if a record exists

问题:

Reading some SQL Tuning 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?

回答1:

It's better to use either of the following:

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

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE 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.

回答2:

I would prefer not use Count function at all:

IF [NOT] EXISTS ( SELECT 1 FROM MyTable WHERE ... )
     <do smth>

---

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

回答3:

You can use:

SELECT 1 FROM MyTable WHERE <MyCondition>

If there is no record matching the condition, the resulted recordset is empty.

回答4:

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.

回答5:

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

回答6:

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.

回答7:

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.

回答8:

I'm using this way:

IIF(EXISTS (SELECT TOP 1 1 
                FROM Users 
                WHERE FirstName = 'John'), 1, 0) AS DoesJohnExist