Problem solve Get help with specific problems with your technologies, process and projects.

Check for the existence of records

This tip helps you quickly find records that meet certain criteria.

When a programmer wants to check only the existence of records that meet certain criteria, he/she will probably use select count(*) from where and then check the count.

I do it more quickly:

For example, in a table called EMP I want to see if there are employees with salaries greater than $10,000:

SELECT COUNT('X') 
FROM DUAL 
WHERE EXISTS (SELECT 'X'
              FROM EMP
              WHERE SALARY > 10000)
However, the select statement in the form of...
  SELECT COUNT('X') 
  FROM DUAL 
  WHERE EXISTS (SELECT 'X' 
                FROM ....
                WHERE .....)

...is more effiecient because in the average case it is 2 times faster and will return 1 for existence and 0 for none.

For More Information


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close