Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-01 |
|
|
View PDF |
An in_condition
is a membership condition. It tests a value for membership in a list of values or subquery
in_conditions::=
expression_list::=
If you use the upper form of the in_condition
condition (with a single expression to the left of the operator), then you must use the upper form of expression_list
. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of expression_list
, and the expressions in each expression_list
must match in number and datatype the expressions to the left of the operator.
See Also:
"Expression Lists"Table 7-12 lists the form of IN
condition.
Type of Condition | Operation | Example |
---|---|---|
IN |
Equal-to-any-member-of test. Equivalent to |
SELECT * FROM employees WHERE job_id IN ('PU_CLERK','SH_CLERK') ORDER BY employee_id; SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id =30) ORDER BY employee_id; |
NOT IN |
Equivalent to != |
SELECT * FROM employees WHERE salary NOT IN (SELECT salary FROM employees WHERE department_id = 30) ORDER BY employee_id; SELECT * FROM employees WHERE job_id NOT IN ('PU_CLERK', 'SH_CLERK') ORDER BY employee_id; |
If any item in the list following a NOT
IN
operation evaluates to null, then all rows evaluate to FALSE
or UNKNOWN
, and no rows are returned. For example, the following statement returns the string 'True
' for each row:
SELECT 'True' FROM employees WHERE department_id NOT IN (10, 20);
However, the following statement returns no rows:
SELECT 'True' FROM employees WHERE department_id NOT IN (10, 20, NULL);
The preceding example returns no rows because the WHERE
clause condition evaluates to:
department_id != 10 AND department_id != 20 AND department_id != null
Because the third condition compares department_id
with a null, it results in an UNKNOWN
, so the entire expression results in FALSE
(for rows with department_id
equal to 10 or 20). This behavior can easily be overlooked, especially when the NOT
IN
operator references a subquery.
Moreover, if a NOT
IN
condition references a subquery that returns no rows at all, then all rows will be returned, as shown in the following example:
SELECT 'True' FROM employees WHERE department_id NOT IN (SELECT 0 FROM DUAL WHERE 1=2);
Restriction on LEVEL in WHERE Clauses In a [NOT
] IN
condition in a WHERE
clause, if the right-hand side of the condition is a subquery, you cannot use LEVEL
on the left-hand side of the condition. However, you can specify LEVEL
in a subquery of the FROM
clause to achieve the same result. For example, the following statement is not valid:
SELECT employee_id, last_name FROM employees WHERE (employee_id, LEVEL) IN (SELECT employee_id, 2 FROM employees) START WITH employee_id = 2 CONNECT BY PRIOR employee_id = manager_id;
But the following statement is valid because it encapsulates the query containing the LEVEL
information in the FROM
clause:
SELECT v.employee_id, v.last_name, v.lev FROM (SELECT employee_id, last_name, LEVEL lev FROM employees v START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id) v WHERE (v.employee_id, v.lev) IN (SELECT employee_id, 2 FROM employees);