All sample questions / SQL

What does SELECT applicant FROM waitlist WHERE priority NOT IN (1, 2) AND ... return? SQL NULL, CASE and Set Operations

Block 4, NULL, CASE and Set Operations, question 2 of the SQL sample

CREATE TABLE waitlist (id INTEGER, applicant TEXT, priority INTEGER);
INSERT INTO waitlist VALUES
  (1, 'App1', 1),
  (2, 'App2', 3),
  (3, 'App3', NULL),
  (4, 'App4', 5);

SELECT applicant FROM waitlist WHERE priority NOT IN (1, 2) AND priority IS NOT NULL

What does this query return?

  1. A. App2 App3 App4
  2. B. App1 App2 App4
  3. C. (no rows)
  4. D. App2 App4
Show the answer and why each option is right or wrong

Answer, D. App2 App4

A. App2 App3 App4
This keeps App3 despite the explicit IS NOT NULL guard, forgetting that a missing priority fails that check regardless of what NOT IN alone would decide.
B. App1 App2 App4
This keeps App1 as if priority 1 slipped past NOT IN (1, 2), but 1 is literally one of the excluded values and fails that test directly.
C. (no rows)
This assumes the NULL priority anywhere in the table defeats the AND for every row, but the IS NOT NULL guard exists precisely to handle that row without affecting the others.
D. App2 App4
Correct. App2 and App4 have a priority outside the excluded list, App1 matches the excluded value 1, and App3 fails the explicit IS NOT NULL guard.

Adding an explicit priority IS NOT NULL guard alongside NOT IN protects against the usual trap, here the list itself has no NULL member so NOT IN alone would already work for the defined priorities, and the guard simply confirms that App3 is dropped because its priority is missing, not because of anything inside the list.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Previous question Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.