SQL EXISTS
Summary: in this tutorial, you will learn how to use the SQL EXISTS
operator to test if a subquery contains any rows.
Introduction to the SQL EXISTS
operator
The EXISTS
the 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
the 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.
SQL EXISTS
operator example
We 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 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
the table in the result set.
SQL 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);
The following screenshot illustrates the result:
SQL EXISTS
and NULL
If the subquery returns NULL
, the EXISTS
the operator still returns the result set. This is because the EXISTS
the operator only checks for the existence of rows 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
the 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.
In this tutorial, you have learned how to use the SQL EXISTS
operator to test for the existence of rows returned by a subquery.
0 Comments
CAN FEEDBACK
Emoji