Existence checking


Link to this posting

Postby Ursego » 22 Feb 2013, 09:41

To check if a record, satisfying the criteria, exists in a table, use EXISTS rather than COUNT

If the record you are looking for is in the beginning of the table, EXISTS stops the table scan immediately after finding it while COUNT keeps scanning the table in full for no reason. Use COUNT only when you really want to know how many rows match the criteria.

*** BAD code: ***

Transact-SQL:
Code: Select all
SELECT @count = COUNT(1) FROM ... WHERE ...
IF @count > 0...

PL/SQL:
Code: Select all
SELECT COUNT(1) INTO v_count FROM...;
IF v_count > 0 THEN...

There is another, much nicer way to define a record existence - the scan will stop immediately after finding the first occurrence:

*** GOOD code: ***

Transact-SQL:
Code: Select all
IF EXISTS (SELECT 'x' FROM ... WHERE ...) ...

PL/SQL:
Code: Select all
SELECT CASE WHEN EXISTS (
    SELECT 'x'
    FROM ...
    WHERE ...
  ) THEN 'Y' ELSE 'N' END INTO v_exists FROM dual;
IF v_exists = 'Y' THEN...
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker