Which of the Following Where Clauses Would Not Select the Number 10?

IN Condition

An in_condition is a membership condition. It tests a value for membership in a list of values or subquery

in_conditions::=

Description of the illustration in_conditions.gif

expression_list::=

Description of the illustration expression_list.gif

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.

Table 7-12 lists the form of IN condition.

Table 7-12 IN 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 ClausesIn 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);        

Which of the Following Where Clauses Would Not Select the Number 10?

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

Share this post