Definition:
The Oracle EXISTS keyword is an operator which is used to correlate records from two participating queries; one of which is an outer SELECT statement, with the other one being an inner sub query. It returns a BOOLEAN output which specifies the result of logical comparison of two records matching on defined set of values.
Note that as soon as a matching record is found in the sub query, the EXISTS conditional statement is set to TRUE and further processing is terminated. For this reason, EXISTS are often used to improve query performance. They are mainly used with correlated sub queries.
Example Syntax:
SELECT
FROM TABLENAME
WHERE EXISTS (sub query)
Example Usage:
The EMPLOYEE table in the query contains employee details. The EMP_TERMINATED table contains a list of employee IDs of employees that have been terminated. The SQL query below displays the details of terminated employees.
SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID, E.SALARY
FROM EMPLOYEE E
WHERE EXISTS (SELECT 1
FROM EMP_TERMINATED
WHERE EMPID = E. EMPLOYEE_ID)
Related Links:
Related Code Snippets:
- EXISTS - Sample of the use of 'EXISTS' clause in SQL
