SELECT statement

Predicates I

Syntax:

N::=
 < expression TO test > [NOT] IN (< subquery >)
 | (< expression1 >,..., < expressionN >)

IN predicate determines whether the value of expression to test matches any value in the subquery or the list. Subquery is an ordinary SELECT statement that has a result set of one column and one or more rows. This column or all expressions in the list must have the same data type as the expression to test. If the target object is equal to any value returned by subquery or is equal to any expression from the comma separated list, the Boolean value of IN predicate is TRUE. If target object <>X for each X in IN clause, the result value is FALSE. If a subquery does not return any rows (empty table), the predicate is FALSE. If none of the above conditions is valid, the predicate is UNKNOWN.

Example 5.2.4

Find the model, processor speed and hard drive capacity for those computers having the hard drive of 10Mb or 20Mb:

Console

Execute

SELECT model, speed, hd 
FROM PC 
WHERE hd IN (10, 20);

SELECT model, speed, hd FROM PC WHERE hd IN (10, 20);

    Scheme

Example 5.2.5

Find the model, processor speed and hard drive capacity for those computers having hard drive of 10Mb or 20Mb and produced by the manufacturer A:

Console

Execute

SELECT model, speed, hd
FROM PC 
WHERE hd IN (10, 20) AND 
 model IN (SELECT model 
 FROM product 
 WHERE maker = 'A'
 );

SELECT model, speed, hd FROM PC WHERE hd IN (10, 20) AND model IN (SELECT model FROM product WHERE maker = 'A' );

    Scheme

Suggested exercises: 1, 5, 8, 23, 25, 27, 38, 57, 71, 80