OCP 1Z0 051 146

146. Evaluate the following SQL statement: 
SQL> SELECT cust_id, cust_last_name 
FROM customers 
WHERE cust_credit_limit IN                        
(select cust_credit_limit                         
FROM customers                         
WHERE cust_city ='Singapore'); 
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. 

SQL> SHOW USERUser is "test"SQL> CREATE TABLE customers  2  AS  3  SELECT DISTINCT cust_credit_limit,cust_city FROM sh.customers;Table createdExecuted in 0.328 secondsSQL> SQL> INSERT INTO customers(cust_credit_limit,cust_city)  2  VALUES(NULL,'Singapore');1 row insertedExecuted in 0 secondsSQL> INSERT INTO customers(cust_credit_limit,cust_city)  2  VALUES(11000,'Singapore');1 row insertedExecuted in 0 secondsSQL> commit;Commit completeExecuted in 0.016 secondsSQL> SQL> SELECT cust_credit_limit, COUNT(*) AS ct  2    FROM customers  3   WHERE cust_credit_limit IN  4         (SELECT cust_credit_limit  5            FROM customers  6           WHERE cust_city = 'Singapore')  7   GROUP BY cust_credit_limit;CUST_CREDIT_LIMIT         CT----------------- ----------            11000        5811 row selected

半连接IN语句，主查询不会返回空值

答案应该选D