Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

Membership Conditions

A membership condition tests for membership in a list or subquery.

membership_condition::=

Text description of conditions9.gif follows
Text description of membership_condition


expression_list::=

Text description of conditions11.gif follows
Text description of expression_list


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.

See Also:

"Expression Lists"

Table 5-7 lists the membership conditions.

Table 5-7  Membership Conditions
Type of Condition Operation Example
IN

"Equal to any member of" test. Equivalent to "= ANY".

SELECT * FROM employees
  WHERE job_id IN
  ('PU_CLERK','SH_CLERK');
SELECT * FROM employees
  WHERE salary IN
  (SELECT salary 
   FROM employees
   WHERE department_id =30);
NOT IN 

Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL.

SELECT * FROM employees
  WHERE salary NOT IN
  (SELECT salary 
   FROM employees
  WHERE department_id = 30);
SELECT * FROM employees
  WHERE job_id NOT IN
  ('PU_CLERK', 'SH_CLERK');

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