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?

Martin Schapendonk · Accepted Answer · 2019-09-26T08:49:45.193

371

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.

edited Sep 26 '19 at 08:49

answered Nov 23 '10 at 08:23

Martin Schapendonk

12,893
3
19
24

1

I will clarify that I intended "unique key" with the "key = value" clause but other than that I'm still behind my answer. – Martin Schapendonk Sep 26 '19 at 08:49
1

OK. With that premise indeed the query would return just one or zero record. BUT: The question does not limit to a unique column. Also: The 2nd query count(1) is equivalent to count(*) from a practical POV. – Martin Ba Sep 27 '19 at 10:54
1

The question says "what's the proper way to verify the existence of A record". I interpreted that as singular, as in: 1 record. The difference between count(*) and count(1) is already covered by my answer. I prefer count(1) because it does not rely on a specific RDBMS implementation. – Martin Schapendonk Sep 27 '19 at 11:14
If `unique_key` is indexed, is it guaranteed that the statement will run strictly under linear time? – McSinyx Mar 08 '23 at 04:27
1

@McSinyx in theory that is correct, although it totally depends on the specific RDBMS implementation that you are running this on. – Martin Schapendonk Mar 09 '23 at 07:19

Pavel Morshenyuk · Answer 2 · 2016-08-11T07:24:43.147

241

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

edited Aug 11 '16 at 07:24

answered Nov 23 '10 at 08:20

Pavel Morshenyuk

10,891
4
32
38

Generaly we use it (the verify) when want do something, then your answer is more complete. – Abner Escócio Nov 07 '18 at 18:43
Good to mention that by using T-SQL – Bronek Jan 18 '19 at 11:25

score 37 · Answer 3 · answered Nov 23 '10 at 08:20

37

You can use:

SELECT 1 FROM MyTable WHERE <MyCondition>

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

answered Nov 23 '10 at 08:20

Cătălin Pitiș

14,123
2
39
62

Did you mean TOP 1? -> (SELECT TOP 1 FROM MyTable WHERE ) – Jacob Oct 21 '15 at 07:38
14

No, I meant exactly "1" – Cătălin Pitiș Oct 21 '15 at 09:59
1

to enable the query optimizer to even knwo that you won't read/need the remaining datasets, you should state SELECT TOP 1 1 FROM... WHERE... (or use the appropriate query hints for your RDBS) – eFloh Jul 13 '16 at 11:45
3

The Exists operator itself tries to retrieve just the absolute minimum of information, so the addition of TOP 1 does nothing except add 5 characters to the query size. - http://www.sqlservercentral.com/blogs/sqlinthewild/2011/04/05/to-top-or-not-to-top-an-exists/ – AquaAlex Jul 20 '16 at 12:16

score 23 · Answer 4 · answered Apr 08 '13 at 17:13

23

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.

answered Apr 08 '13 at 17:13

oski

261
2
3

In case of `SELECT TOP 1` will it actually terminate after finding one or does it continue to find all to be able to say which one is TOP? – Eirik H Mar 13 '14 at 11:05
6

PS: To be sure I always `IF EXISTS (SELECT TOP 1 1 FROM ... WHERE ..)` – Eirik H Mar 13 '14 at 11:12
the Star operator will force the DBMS to access the clustered index instead of just the index(es) that will be needed for your join condition. so it's better to use a constant valua as result, i.e. select top 1 1 .... That will return 1 or DB-Null, depending on the condition is a match or not. – eFloh Jul 13 '16 at 11:48
it's nice. I like the first one. – isxaker Aug 24 '18 at 12:03

score 22 · Answer 5 · edited Mar 06 '18 at 03:02

22

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.

edited Mar 06 '18 at 03:02

AAEM

1,837
2
18
26

answered Jan 14 '15 at 09:54

user3059943

221
2
3

10

Good point but Limit works on MySQL and PostgreSQL, top works on SQL Server, you should note it on your answer – Leo Gurdian Nov 08 '18 at 22:27
`unnecessary ` with respected to ? – Gray Programmerz Jul 16 '22 at 13:55

score 16 · Answer 6 · answered Nov 23 '10 at 08:22

16

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.

answered Nov 23 '10 at 08:22

JesseW

1,255
11
19

3

If any "check for existence" query returns more than one row, I think it is more useful to double check your WHERE clause instead of LIMIT-ing the number of results. – Martin Schapendonk Nov 23 '10 at 08:25
2

I think Limit is used in Oracle and not in SQL Server – Shantanu Gupta Nov 23 '10 at 08:26
7

I'm considering the case where they can legitimately be multiple rows -- where the question is: "Is there (one or more) rows that satisfy this condition?" In that case, you don't want to look at all of them, just one. – JesseW Nov 23 '10 at 08:26
1

@Shantanu -- I know, that's why I linked to the (very through) en.wikipedia article explaining the other forms. – JesseW Nov 23 '10 at 08:27

Winston Smith · Answer 7 · 2010-11-23T09:58:33.053

14

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

edited Nov 23 '10 at 09:58

answered Nov 23 '10 at 08:18

Winston Smith

21,585
10
60
75

2

You're making the mistaken assumption that a DBMS will somehow check all those columns. The performance difference between `count(1)` and `count(*)` will be different only in the most brain-dead DBMS. – paxdiablo Nov 23 '10 at 08:20
**@paxdiablo** are you suggesting that relying on implementation details is a good idea? – Winston Smith Nov 23 '10 at 08:24
2

No, I'm saying that _you_ are actually relying on implementation details when you state it'll be more efficient. If you really want to ensure you get the best performance, you should profile it for the specific implementation using representative data, or just forget about it totally. Anything else is potentially misleading, and could change drastically when moving (for example) from DB2 to MySQL. – paxdiablo Nov 23 '10 at 08:30
1

I want to make it clear I'm not dissing your answer. It _is_ useful. The only bit I take issue with is the efficiency claim since we've _done_ evaluations in DB2/z and found there's no real difference between `count(*)` and `count(1)`. Whether that's the case for _other_ DBMS', I can't say. – paxdiablo Nov 23 '10 at 08:37
5

*"Anything else is potentially misleading, and could change drastically when moving (for example) from DB2 to MySQL"* You're much more likely to get bitten by performance degradation of SELECT COUNT(*) when moving DBMS than an implementation difference in SELECT 1 or COUNT(1). I'm a firm believer in writing the code which most clearly expresses exactly what you want to achieve, rather than relying on optimizers or compilers to default to your desired behaviour. – Winston Smith Nov 23 '10 at 08:55
2

Misleading statement "COUNT(*)" means 'count the rows' full stop. It does not require access to any particular column. And in most cases will not even require access to the row itself as a count any unique index is sufficient. – James Anderson Nov 23 '10 at 09:03

score 3 · Answer 8 · answered Jul 19 '19 at 19:36

3

Other option:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [MyTable] AS [MyRecord])
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END

answered Jul 19 '19 at 19:36

Pranav

105
5

what is the purpose of `CAST(1 AS BIT)` ? Why can't I just write `THEN 1 ELSE 0` ? – Sumit Aug 12 '21 at 22:15
You defiantly can return 1 or 0. It's all about what type of result you want in the end. I wanted to return boolean instead of numeric value. – Pranav Aug 14 '21 at 02:52

score 2 · Answer 9 · edited Oct 12 '21 at 14:06

2

I'm using this way:

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

edited Oct 12 '21 at 14:06

Pranav

105
5

answered May 23 '18 at 10:04

DiPix

5,755
15
61
108

SQL: How to properly check if a record exists

9 Answers9

Linked

Related