IN predicate

Syntax:

IN::=
< 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

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

Example 5.2.5

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

Suggested exercises: 1, 5, 8, 23, 25, 27, 38, 57, 71, 80, 89, 90, 103, 127, 129