EXISTS and NOT EXISTS

EXISTS and NOT EXISTS
The keywords EXISTS and NOT EXISTS are designed for use only with subqueries. They
produce a simple true/false result. EXISTS is true if and only if there exists at least one
row in the result table returned by the subquery; it is false if the subquery returns an empty
result table. NOT EXISTS is the opposite of EXISTS. Since EXISTS and NOT EXISTS
check only for the existence or non-existence of rows in the subquery result table, the
subquery can contain any number of columns. For simplicity it is common for subqueries
following one of these keywords to be of the form:
(SELECT * FROM . . . )
Example 5.31 Query using EXISTS
Find all staff who work in a London branch office.
SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS (SELECT *
FROM Branch b
WHERE s.branchNo = b.branchNo AND city = ‘London’);
This query could be rephrased as ‘Find all staff such that there exists a Branch row containing
his/her branch number, branchNo, and the branch city equal to London’. The test for
inclusion is the existence of such a row. If it exists, the subquery evaluates to true. The
result table is shown in Table 5.31.
Note that the first part of the search condition s.branchNo = b.branchNo is necessary to
ensure that we consider the correct branch row for each member of staff. If we omitted this
part of the query, we would get all staff rows listed out because the subquery (SELECT *
FROM Branch WHERE city = ‘London’) would always be true and the query would be
reduced to:
SELECT staffNo, fName, lName, position FROM Staff WHERE true;
which is equivalent to:
SELECT staffNo, fName, lName, position FROM Staff;
We could also have written this query using the join construct:
SELECT staffNo, fName, lName, position
FROM Staff s, Branch b
WHERE s.branchNo = b.branchNo AND city = ‘London’;