Page 1 of 1

Existence checking

PostPosted: 22 Feb 2013, 09:41
by Ursego
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...