Evaluate the following SQL statement: 
Which statement is true regarding the above query if one of the values generated by the subquery
is null?
A.
It produces an error.
B.
It executes but returns no rows.
C.
It generates output for null as well as the other values produced by the subquery.
D.
It ignores the null value and generates output for the other values produced by the subquery.
Explanation:
D is correct here.
SQL> SELECT ‘True’ FROM employees WHERE department_id IN (10, 20, null);
—-
True
True
True
SQL> SELECT ‘True’ FROM employees WHERE department_id IN (10, 20);
—-
True
True
True
I think also.
You are right. It ignores the NULL values.
You are right. The answer should be D.
Using the IN operator, Oracle will compare all values in “IN” operator and return rows if the value is the same, i.e. return true. But null is not comparable. It always return null. No rows will be returned for null=null.
Tested. Answer should be D only
Answer should be D.
“WHERE cust_credit_limit IN (NULL)” does not give any output, NULL values are ignored.
To test NULL values in a WHERE clause, one has to use “WHERE cust_credit_limit IS NULL”.
answer should be D
Yes, i am also checked. Answer is D.
Correct is D, tested against hr schema:
SQL> select employee_id,department_id,manager_id from employees where department
2 ;
EMPLOYEE_ID DEPARTMENT_ID MANAGER_ID
———– ————- ———-
100 90
101 90 100
102 90 100
SQL> select employee_id from employees where department_id=90 and manager_id in (select manager_id from employees where department_id = 90);
EMPLOYEE_ID
———–
102
101
Delaware Registered Sex Offenders
http://www.dYNihcArR6.com/dYNihcArR6
D – tested