EXISTS / NOT EXISTS
Purpose
Tests a specified table (normally a derived table) for the existence of at least one row (that is, it tests whether the table in question is non-empty).
EXISTS is supported as the predicate of the search condition in a WHERE clause.
Syntax
ANSI Compliance
EXISTS and NOT EXISTS are ANSI SQL:2011 compliant.
Usage Notes
The function of the EXISTS predicate is to test the result of subquery.
If execution of the subquery returns response rows then the where condition is considered satisfied.
Note that use of the NOT qualifier for the EXISTS predicate reverses the sense of the test. Execution of the subquery does not, in fact, return any response rows. Instead, it returns a boolean result to indicate whether responses would or would not have been returned had they been requested.
Subquery Restrictions
The subquery cannot specify a SELECT AND CONSUME statement.
Relationship Between EXISTS/NOT EXISTS and IN/NOT IN
EXISTS predicate tests the existence of specified rows of a subquery. In general, EXISTS can be used to replace comparisons with IN and NOT EXISTS can be used to replace comparisons with NOT IN. However, the reverse is not true. Some problems can be solved only by using EXISTS and/or NOT EXISTS predicate. For an example, see “For ALL” on page 939.
For information on IN and NOT IN, see “IN/NOT IN” on page 943.
Example
To select rows of t1 whose values in column x1 are equal to the value in column x2 of t2, one of the following queries can be used:
SELECT *
FROM t1
WHERE x1 IN
(SELECT x2
FROM t2);
SELECT *
FROM t1
WHERE EXISTS
(SELECT *
FROM t2
WHERE t1.x1=t2.x2);
To select rows of t1 whose values in column x1 are not equal to any value in column x2 of t2, you can use any one of the following queries:
SELECT *
FROM t1
WHERE x1 NOT IN
(SELECT x2
FROM t2);
SELECT *
FROM t1
WHERE NOT EXISTS
(SELECT *
FROM t2
WHERE t1.x1=t2.x2);
SELECT 'T1 is not empty'
WHERE EXISTS
(SELECT *
FROM t1);
SELECT 'T1 is empty'
WHERE NOT EXISTS
(SELECT *
FROM t1);
EXISTS Predicate Versus NOT IN and Nulls
Use the NOT EXISTS predicate instead of NOT IN if the following conditions are true:
For example, if all of the previous conditions are true for the following query, use NOT EXISTS instead of NOT IN:
SELECT dept, DeptName
FROM Department
WHERE Dept NOT IN
(SELECT Dept
FROM Course);
The NOT EXISTS version looks like this:
SELECT dept, DeptName
FROM Department
WHERE NOT EXISTS
(SELECT Dept
FROM Course
WHERE Course.Dept=Department.Dept);
That is, either Course.Dept or Department.Dept is nullable and a row from Department with a null for Dept should be returned and a null in Course.Dept should not prevent rows from Department from being returned.
For ALL
Two nested NOT EXISTS can be used to express a SELECT statement that embodies the notion of “for all (logical ∀) the values in a column, there exists (logical ∃) …”
For example the query to select a ‘true’ value if the library has at least one book for all the publishers can be expressed as follows:
SELECT 'TRUE'
WHERE NOT EXISTS
(SELECT *
FROM publisher pb
WHERE NOT EXISTS
(SELECT *
FROM book bk
WHERE pb.PubNum=bk.PubNum);
NOT EXISTS Clauses and Stored Procedures
You cannot specify a NOT EXISTS clause in a stored procedure conditional expression if that expression also references an alias for a local variable, parameter, or cursor.
NOT EXISTS and Recursive Queries
NOT EXISTS cannot appear in a recursive statement of a recursive query. However, a non-recursive seed statement in a recursive query can specify the NOT EXISTS predicate.
Example : EXISTS with Correlated Subqueries
Select all student names who have registered in at least one class offered by some department.
SELECT SName, SNo
FROM student s
WHERE EXISTS
(SELECT *
FROM department d
WHERE EXISTS
(SELECT *
FROM course c, registration r, class cl
WHERE c.Dept=d.Dept
AND c.CNo=r.CNo
AND s.SNo=r.SNo
AND r.CNo=cl.CNo
AND r.Sec=cl.Sec));
The content of the student table is as follows:
|
Sname |
SNo |
|
Helen Chu |
1 |
|
Alice Clark |
2 |
|
Kathy Kim |
3 |
|
Tom Brown |
4 |
The content of the department table is as follows:
|
Dept |
DeptName |
|
100 |
Computer Science |
|
200 |
Physic |
|
300 |
Math |
|
400 |
Science |
The content of course table is as follows:
|
CNo |
Dept |
|
10 |
100 |
|
11 |
100 |
|
12 |
200 |
|
13 |
200 |
|
14 |
300 |
The content of the class table is as follows:
|
CNo |
Sec |
|
10 |
1 |
|
11 |
1 |
|
12 |
1 |
|
13 |
1 |
|
14 |
1 |
The content of the registration table is as follows:
|
CNo |
SNo |
Sec |
|
10 |
1 |
1 |
|
10 |
2 |
1 |
|
11 |
3 |
1 |
|
12 |
1 |
1 |
|
13 |
2 |
1 |
|
14 |
1 |
1 |
The following rows are returned:
SName SNo
----------- ---
Helen Chu1 *
Alice Clark 2
Kathy Kim 3
For a full explanation of correlated subqueries, see “Correlated Subqueries” in SQL Data Manipulation Language.
Example : NOT EXISTS with Correlated Subqueries
Select the names of all students who have registered in at least one class offered by each department that offers a course.
SELECT SName, SNo
FROM student s
WHERE NOT EXISTS
(SELECT *
FROM department d
WHERE d.Dept IN
(SELECT Dept
FROM course) AND NOT EXISTS
(SELECT *
FROM course c, registration r, class cl
WHERE c.Dept=d.Dept
AND c.CNo=r.CNo
AND s.SNo=r.SNo
AND r.CNo=cl.CNo
AND r.Sec=cl.Sec)));
With the contents of the tables as in “Example 1: EXISTS with Correlated Subqueries” on page 939, the following rows are returned:
SName SNo
----- ---
Helen Chu 1