20:41

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

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

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);

model	speed	hd
1233	750	20
1232	500	10
1232	450	10
1260	500	10
1233	800	20

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'
 );

model	speed	hd
1233	750	20
1232	500	10
1232	450	10
1233	800	20

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

IN predicates NOT IN

Previous [BETWEEN predicate]

[Renaming the columns and calculations in the result set] Next

Last added:

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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Predicates I

IN predicate