SELECT statement

Conventional set-theoretic operations and the SELECT statement

The syntax:

EXISTS::=
 [NOT] EXISTS (< TABLE subquery >)

The EXISTS predicate evaluates to TRUE providing the subquery contains any rows, otherwise it evaluates to FALSE. NOT EXISTS works the same as EXISTS being satisfied if no rows are returnable by the subquery. This predicate does not evaluate to UNKNOWN.

As in our case, the EXISTS predicate is generally used with correlated subqueries. That subquery type has an outer reference to the value in the main query. The subquery result may be dependent on this value and must be separately evaluated for each row of the query that includes the subquery. Because of this, the EXISTS predicate may have different values for each row of the main query.

Intersection example.

Find those laptop makers who also produce printers:

Console

Execute

SELECT DISTINCT maker
FROM Product AS Lap_product
WHERE type = 'laptop' AND 
 EXISTS (SELECT maker
 FROM Product
 WHERE type = 'printer' AND 
 maker = Lap_product.maker
 );

SELECT DISTINCT maker FROM Product AS Lap_product WHERE type = 'laptop' AND EXISTS (SELECT maker FROM Product WHERE type = 'printer' AND maker = Lap_product.maker );

    Scheme

The printer makers are retrieved by the subquery and compared with the maker returned from the main query. The main query returns the laptop makers. So, for each laptop maker it is checked that the subquery returns any rows (i.e. this maker also produces printers). Because the two queries in the WHERE clause must simultaneously be satisfied (AND), the result set includes only wanted rows. The DISTINCT keyword is used to make sure each maker is in the returned data only once. As a result, we get:

Exception example.

Find those laptop makers who do not produce printers:

Console

Execute

SELECT DISTINCT maker
FROM Product AS Lap_product
WHERE type = 'laptop' AND 
 NOT EXISTS (SELECT maker
 FROM Product
 WHERE type = 'printer' AND 
 maker = Lap_product.maker
 );

SELECT DISTINCT maker FROM Product AS Lap_product WHERE type = 'laptop' AND NOT EXISTS (SELECT maker FROM Product WHERE type = 'printer' AND maker = Lap_product.maker );

    Scheme

Here, it is sufficient to replace EXISTS in the previous example with NOT EXISTS. So, the returned data includes only those main query rows, for which the subquery return no rows. As a result we get: