Curriculum
In this tutorial, you will learn how to use the SQL EXISTS
 operator to test if a subquery contains any rows.
EXISTS
 operatorThe EXISTS
 operator allows you to specify a subquery to test for the existence of rows. The following illustrates the syntax of the EXISTS
 operator:
EXISTS (subquery)
The EXISTS
 operator returns true if the subquery contains any rows. Otherwise, it returns false.
The EXISTS
 operator terminates the query processing immediately once it finds a row, therefore, you can leverage this feature of the EXISTS
 operator to improve the query performance.
EXISTS
 operator exampleWe will use the  employees
 and dependents
 tables in the sample database for the demonstration.
The following statement finds all employees who have at least one dependent:
SELECT employee_id, first_name, last_name FROM employees WHERE EXISTS( SELECT 1 FROM dependents WHERE dependents.employee_id = employees.employee_id);
The subquery is correlated. For each row in the  employees
 table, the subquery checks if there is a corresponding row in the dependents
 table. If yes, then the subquery returns one which makes the outer query to include the current row in the  employees
 table. If there is no corresponding row, then the subquery returns no row that causes the outer query to not include the current row in the  employees
 table in the result set.
NOT EXISTS
To negate the EXISTS
 operator, you use the NOT
 operator as follows:
NOT EXISTS (subquery)
For example, the following query finds employees who do not have any dependents:
SELECT employee_id, first_name, last_name FROM employees WHERE NOT EXISTS( SELECT 1 FROM dependents WHERE dependents.employee_id = employees.employee_id);
EXISTS
 and NULL
If the subquery returns NULL
, the EXISTS
 operator still returns the result set. This is because the EXISTS
 operator only checks for the existence of row returned by the subquery. It does not matter if the row is NULL
 or not.
In the following example, the subquery returns NULL
 but the EXISTS
 operator still evaluates to true:
SELECT employee_id, first_name, last_name FROM employees WHERE EXISTS( SELECT NULL) ORDER BY first_name , last_name;
The query returns all rows in the  employees
 table.