loading..
Русский    English
22:19

IN predicate

Syntax:

  1. IN::=
  2. < expression TO test > [NOT] IN (< subquery >)
  3. | (< 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
  1. SELECT model, speed, hd
  2. FROM PC
  3. 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
  1. SELECT model, speed, hd
  2. FROM PC
  3. WHERE hd IN (10, 20) AND
  4. model IN (SELECT model
  5. FROM product
  6. WHERE maker = 'A'
  7. );

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


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 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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.