Question:

Consider the following two query results:

SELECT count(*) AS total FROM orders;

+-------+
| total |
+-------+
|  100  |
+-------+

SELECT count(*) AS cust_123_total FROM orders WHERE customer_id = '123';

+----------------+
| cust_123_total |
+----------------+
|       15       |
+----------------+

Given the above query results, what will be the result of the query below?

SELECT count(*) AS cust_not_123_total FROM orders WHERE customer_id <> '123'

Answer:

The obvious answer is 85 (i.e, 100 - 15). However, that is not necessarily correct. Specifically, any records with a customer_id of NULL will not be included in either count (i.e., they won’t be included in cust_123_total, nor will they be included in cust_not_123_total). For example, if exactly one of the 100 customers has a NULL customer_id, the result of the last query will be:

+--------- ----------+
| cust_not_123_total |
+--------------------+
|         84         |
+--------------------+

Keywords:

© 2017 QuizBucket.org