Existence checking


Link to this posting

Postby Ursego » 22 Feb 2013, 09:41

Use EXISTS instead of COUNT to check if a record, satisfying your search criteria, exists in a table.

You should use COUNT only when you need to answer the question: "How many rows match a certain criteria?". If you use COUNT to answer the question "Is there at least one row matching a certain criteria?", you are asking the RDBMS to do lots of unnecessary work.

Elegant programming means not only producing a code which is elegant cosmetically and easy to read. For example, inefficient code doesn't look very elegant to me even if it is perfect cosmetically... I don't feel comfortable seeing the following construction which, potentially, scans a huge amount of records:

*** 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 record existence - the scan will stop immediately after finding the first occurrence:

*** GOOD code: ***

Transact-SQL:
Code: Select all
SET @exists = 0
IF EXISTS (SELECT 'x' FROM ... WHERE ...)
   SET @exists = 1

IF @exists = 1...

PL/SQL:
Code: Select all
BEGIN
   SELECT 1
     INTO v_exists
     FROM DUAL
    WHERE EXISTS (SELECT 'x' FROM... WHERE ...);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      v_exists := 0;
END;
IF v_exists = 1 THEN...
User avatar
Ursego
Site Admin
 
Posts: 127
Joined: 19 Feb 2013, 20:33



IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
free counters

eXTReMe Tracker