|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Conditions, 5 of 13
A membership condition tests for membership in a list or subquery.
If you use the upper form of this 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.
Table 5-7 lists the membership conditions.
|Type of Condition||Operation||Example|
"Equal to any member of" test. Equivalent to "
Equivalent to "!=
If any item in the list following a
IN operation evaluates to null, then all rows evaluate to
UNKNOWN, and no rows are returned. For example, the following statement returns the string '
TRUE' for each row:
However, the following statement returns no rows:
The preceding example returns no rows because the
WHERE clause condition evaluates to:
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
IN operator references a subquery.
Moreover, if a
IN condition references a subquery that returns no rows at all, then all rows will be returned, as shown in the following example:
Restriction on LEVEL in WHERE clauses: In a [
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