loading..
Русский    English
13:46

EXISTS predicate

The syntax:

  1. EXISTS::=
  2. [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
  1. SELECT DISTINCT maker
  2. FROM Product AS Lap_product
  3. WHERE type = 'laptop' AND
  4. EXISTS (SELECT maker
  5. FROM Product
  6. WHERE type = 'printer' AND
  7. maker = Lap_product.maker
  8. );

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:

maker
A

Exception example.

Find those laptop makers who do not produce printers:

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product AS Lap_product
  3. WHERE type = 'laptop' AND
  4. NOT EXISTS (SELECT maker
  5. FROM Product
  6. WHERE type = 'printer' AND
  7. maker = Lap_product.maker
  8. );

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:

maker
B
C

Suggested exercises: 27, 36, 39, 128

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100