What does SELECT id FROM codes WHERE (a = 1 OR a = 2) AND NOT b = 3 return? SQL Selecting, Filtering and Sorting

Block 1, Selecting, Filtering and Sorting, question 14 of the SQL sample

CREATE TABLE codes (id INTEGER, a INTEGER, b INTEGER);
INSERT INTO codes VALUES
  (1, 1, 3),
  (2, 2, 3),
  (3, 3, 3),
  (4, 1, 1);

SELECT id FROM codes WHERE (a = 1 OR a = 2) AND NOT b = 3

What does this query return?

A. (no rows)
B. 4
C. 1 4
D. 1 2

Show the answer and why each option is right or wrong

Answer, B. 4

A. (no rows): This treats NOT b = 3 as a check for a missing value, as if it meant b IS NULL. NOT in front of an equality reverses a comparison between actual values, it does not test for NULL.
B. 4: Correct. Only row 4 has a in 1, 2 and, at the same time, a b that is not 3.
C. 1 4: This regroups the condition as a = 1 OR (a = 2 AND NOT b = 3), moving the parentheses away from the OR. The query as written groups a = 1 OR a = 2 first, and applies NOT b = 3 to that whole group with AND.
D. 1 2: This drops the NOT and keeps the plain equality b = 3, so it keeps rows that match b = 3 instead of rows that avoid it. NOT reverses the condition it applies to, here it asks for rows where b is not 3.

NOT applies to the single condition or parenthesized group written right after it, and it reverses that condition rather than testing for a missing value. Parentheses fix which conditions belong to the OR, moving them changes which rows satisfy the AND that follows.

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.