ansaurus

Question

Should I use If exist or Count in this statement?

Answer 1

+2 A:

This statement:

SELECT  COUNT(*) AS found
FROM    names
WHERE   name = 'Hulk'

will return you the total number of records for 'Hulk'

This statement:

SELECT  1 AS found
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    names
        WHERE   name = 'Hulk'
        )

will return 1 if at least one record exists, otherwise it will return nothing.

If you just need to check that at least one record exists, the latter query is more efficient.

Quassnoi 2010-03-01 11:08:13

@OP: And if you need to use `select count` for some reason, don't use `count(*)`, you're making the RDBMS work too hard. Use `count(name)` (in this case). That way, if the information is in an index, it can use it. (Although I'd expect systems to optimize it for you, I'm frequently surprised by what systems don't optimize for you, so...)

T.J. Crowder 2010-03-01 11:10:16

@TJCrowder: all major systems will optimize `COUNT(*)` in best possible way. In this very case, `COUNT(name)` will be of same semantics and performance, but in general, the system will need to retrieve `name` to make sure it is not null, so it will be less efficient and possibly even produce the wrong results.

Quassnoi 2010-03-01 11:18:08

Thank you all . Stackoverflow rocks !!!

ahmed 2010-03-01 11:53:54

ansaurus

tags:

views:

answers:

Should I use If exist or Count in this statement?

related questions