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> 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 USER
User is "test"
SQL> CREATE TABLE customers
2 AS
3 SELECT DISTINCT cust_credit_limit,cust_city FROM sh.customers;
Table created
Executed in 0.328 seconds
SQL>
SQL> INSERT INTO customers(cust_credit_limit,cust_city)
2 VALUES(NULL,'Singapore');
1 row inserted
Executed in 0 seconds
SQL> INSERT INTO customers(cust_credit_limit,cust_city)
2 VALUES(11000,'Singapore');
1 row inserted
Executed in 0 seconds
SQL> commit;
Commit complete
Executed in 0.016 seconds
SQL>
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 581
1 row selected
半连接IN语句,主查询不会返回空值
答案应该选D