SQL Quiz: Three Valued Logic And Null
15 questions · exam conditions
0:00
Three Valued Logic And NullQuestion 1 of 15

Query has WHERE NOT (salary > 50000). Which rows are selected?

salary <= 50000; no NULLs
salary <= 50000; NULLs too
salary >= 50000; no NULLs
salary >= 50000; NULLs too
← Back to quizzes

SQL Quiz

SQL Quiz: Three Valued Logic And Null

Practice Three Valued Logic And Null in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Three Valued Logic And Null, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Query has WHERE NOT (salary > 50000). Which rows are selected?

  1. salary <= 50000; no NULLs (correct answer)
  2. salary <= 50000; NULLs too
  3. salary >= 50000; no NULLs
  4. salary >= 50000; NULLs too
Explanation: NOT (salary > 50000) is true only when salary > 50000 is false, which happens when salary is less than or equal to 50000. In SQL, any comparison with NULL yields UNKNOWN, and NOT UNKNOWN is still UNKNOWN, so NULL salaries are not selected. The tempting mistake is to include NULLs, but they fail the WHERE condition.

Question 2

What does (a = 1 AND b = 1) yield when a=1 and b is NULL?

  1. TRUE, because a equals 1
  2. FALSE, because b is NULL
  3. UNKNOWN, because b is NULL (correct answer)
  4. NULL, because b is NULL
Explanation: With a=1, the condition a=1 is TRUE. But b is NULL, so b=1 is UNKNOWN, not FALSE. In SQL, TRUE AND UNKNOWN yields UNKNOWN. The tempting wrong answer is FALSE because b is NULL, but comparing a NULL to any value never evaluates to FALSE; it produces UNKNOWN.

Question 3

What does NOT (a = 1 OR b = 2) yield when a=1 and b is NULL?

  1. TRUE; a=1 makes the OR TRUE
  2. FALSE; a=1 makes the OR TRUE (correct answer)
  3. UNKNOWN; b is NULL in the OR
  4. NULL; b is NULL in the OR
Explanation: With a=1, the condition a = 1 is TRUE. In SQL, TRUE OR UNKNOWN is TRUE, so the whole OR is TRUE. Applying NOT gives FALSE. The tempting mistake is choosing UNKNOWN or NULL because b is NULL, but b's value cannot override a TRUE operand in an OR.

Question 4

In SQL, what is x > 5 OR x <= 5 when x is NULL?

  1. TRUE; all values fit a side
  2. FALSE; NULL has no side
  3. NULL; because x is NULL
  4. UNKNOWN; each side unknown (correct answer)
Explanation: When x is NULL, both comparisons x > 5 and x <= 5 evaluate to UNKNOWN, not TRUE or FALSE. Combining UNKNOWN with OR gives UNKNOWN, so the whole expression is UNKNOWN. The tempting wrong answer is NULL; that confuses SQL's NULL value with UNKNOWN, which is a separate truth value for comparisons.

Question 5

Which is true of x NOT IN (1, NULL) for any x?

  1. It never evaluates to TRUE (correct answer)
  2. It is TRUE whenever x is 2
  3. It is TRUE when x is NULL
  4. It is FALSE when x is 2
Explanation: In SQL, x NOT IN (1, NULL) means NOT (x = 1 OR x = NULL). Comparing anything to NULL yields UNKNOWN, not TRUE or FALSE. So even when x is 2, the result is UNKNOWN, never TRUE. The tempting wrong answer is that it is TRUE whenever x is 2, but that ignores NULL's UNKNOWN behavior.

Question 6

A table has four rows whose status values are 'open', 'closed', NULL, and 'pending'.

How many rows are returned by this query?

SELECT * FROM tasks WHERE status = 'open' OR status <> 'open';

  1. One row, because only 'open' makes the first comparison TRUE
  2. Two rows, because the comparisons cancel for some non-NULL values
  3. Three rows, because every non-NULL status makes one comparison TRUE (correct answer)
  4. Four rows, because a condition or its negation is always TRUE
Explanation: Whenever you see a query combining = and <> comparisons in SQL, the key concept to keep in mind is three-valued logic: SQL comparisons don't just return TRUE or FALSE — they can return UNKNOWN when NULL is involved. Walk through each row in the table. For 'open': status = 'open' is TRUE, so the row is returned. For 'closed': status = 'open' is FALSE, but status <> 'open' is TRUE, so the row is returned. For 'pending': same logic — the <> condition is TRUE, so the row is returned. That's three rows total, confirming C is correct. The fourth row, NULL, is the exception: both NULL = 'open' and NULL <> 'open' evaluate to UNKNOWN, not TRUE, so the WHERE clause rejects that row entirely. A is wrong because it assumes only 'open' satisfies the full condition — but 'closed' and 'pending' both satisfy the <> 'open' side. B is wrong because the two comparisons don't "cancel" for non-NULL values; they complement each other, meaning at least one is always TRUE for any non-NULL string. D is the trickiest trap — in classical boolean logic, P OR NOT P is always TRUE, but SQL's three-valued logic breaks this rule. NULL makes both sides UNKNOWN, so the OR expression is also UNKNOWN, not TRUE, and the row is filtered out. Study tip: Anytime you see NULL in SQL filter conditions, remember it's a "poison" — any comparison with NULL yields UNKNOWN, and UNKNOWN in a WHERE clause means the row is excluded. Never assume x <> value catches NULLs.

Question 7

The items table contains non-NULL item_id values 1, 2, and 3. A subquery against blocklist returns exactly two values: 2 and NULL.

Which item IDs are returned by this filter?

WHERE item_id NOT IN (SELECT item_id FROM blocklist)

  1. Item IDs 1 and 3 only
  2. Item ID 2 only
  3. All three item IDs
  4. No item IDs (correct answer)
Explanation: Whenever you see NOT IN paired with a subquery, your first instinct should be to check whether that subquery can return NULL. This is one of SQL's most notorious traps. Here's why: SQL uses three-valued logic — expressions evaluate to TRUE, FALSE, or UNKNOWN. When you write item_id NOT IN (2, NULL), SQL expands this into comparisons: item_id <> 2 AND item_id <> NULL. Any comparison with NULL produces UNKNOWN, not TRUE or FALSE. Since WHERE only passes rows that evaluate to TRUE, a result of UNKNOWN causes the row to be silently excluded. So for every item in your table: item 1 evaluates as 1 <> 2 (TRUE) AND 1 <> NULL (UNKNOWN) → UNKNOWN. Item 2 evaluates as 2 <> 2 (FALSE) → short-circuits to FALSE. Item 3 evaluates as 3 <> 2 (TRUE) AND 3 <> NULL (UNKNOWN) → UNKNOWN. Not a single row passes, confirming D is correct — no item IDs are returned. Answer A assumes items 1 and 3 pass through normally, ignoring that the NULL in the subquery poisons every comparison involving those rows. Answer B incorrectly suggests 2 is returned, when in fact it's the one value that definitively fails the NOT IN check. Answer C assumes NULL is harmlessly ignored, which is the opposite of reality. The study tip: never use NOT IN with a subquery unless you're certain that column contains no NULLs. Prefer NOT EXISTS instead — it handles NULLs safely and produces the intuitive result.

Question 8

The customers table contains customer 1 with region = NULL and customer 2 with region = 'east'. The orders table contains order 101 for customer 1 with region = NULL and order 102 for customer 2 with region = 'east'.

Which customers are returned by this query?

SELECT c.customer_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.region = c.region WHERE o.order_id IS NULL;

  1. Customer 1 only (correct answer)
  2. Customer 2 only
  3. Both customers 1 and 2
  4. Neither customer 1 nor 2
Explanation: When you see a LEFT JOIN combined with a WHERE clause filtering for NULLs, you're looking at a pattern that finds unmatched rows — but NULL comparison in the JOIN condition adds a critical twist. In a LEFT JOIN, every row from the left table (customers) is preserved. The JOIN condition o.customer_id = c.customer_id AND o.region = c.region must evaluate to TRUE for a match. Here's the trap: NULL does not equal NULL in SQL. When customer 1 has region = NULL and order 101 also has region = NULL, the comparison NULL = NULL evaluates to UNKNOWN, not TRUE. So no match is made, and order 101's columns appear as NULL in the result. The WHERE o.order_id IS NULL filter then keeps customer 1, since no order was successfully joined. For customer 2, both sides have region = 'east', so the JOIN condition evaluates to TRUE, order 102 matches, and o.order_id is 102 — not NULL. The WHERE clause filters customer 2 out. This confirms A is correct: only customer 1 is returned. Choice B is wrong because customer 2 successfully joins to order 102, so it's eliminated by the WHERE clause. Choice C fails for the same reason — customer 2 does match. Choice D incorrectly assumes customer 1 also matches, which it doesn't because of NULL's behavior in equality comparisons. Study tip: Anytime you use a column that might contain NULLs in a JOIN condition, remember that NULL = NULL is never TRUE — use IS NOT DISTINCT FROM if you want NULLs to match NULLs.

Question 9

A subquery returns exactly the values 3 and NULL. Candidate rows have x values 2, 4, and NULL.

Which candidate values pass the following standard SQL predicate?

x > ALL (subquery)

  1. The value 4 only
  2. The values 2 and 4 only
  3. The value NULL only
  4. No candidate values (correct answer)
Explanation: When you see x > ALL (subquery) in SQL, remember that this predicate must hold true against every value the subquery returns — including NULL. This is where most students get tripped up. Here's the critical rule: any comparison involving NULL in SQL produces UNKNOWN, not TRUE or FALSE. For x > ALL (subquery), SQL internally evaluates x > 3 AND x > NULL. The second comparison always yields UNKNOWN, which means the entire ALL predicate can never evaluate to TRUE — for any candidate value. Let's walk through each candidate: 4 > 3 is TRUE, but 4 > NULL is UNKNOWN, so 4 fails. 2 > 3 is already FALSE, so 2 fails immediately. NULL > 3 is UNKNOWN, so NULL fails too. No candidate passes, confirming D is correct. Choice A is the classic trap — students assume 4 is the only value greater than 3 in the subquery and stop there, forgetting the NULL in the subquery poisons every comparison. Choice B compounds that mistake by also including 2, which isn't even greater than 3. Choice C reflects a different misunderstanding — thinking NULL behaves symmetrically and "cancels out" against the NULL in the subquery, but NULL comparisons don't work that way. The study tip to remember: a NULL anywhere in an ALL subquery's result set guarantees no rows pass, because you can never prove the comparison holds against an unknown value. Treat NULL in subqueries as a silent killer for ALL predicates — always check whether the subquery could return NULL before predicting results.

Question 10

A table contains three rows whose amount values are 150, 100, and NULL.

Which values are returned by this standard SQL filter?

WHERE (amount > 100) IS NOT TRUE

  1. 100 only
  2. 150 and NULL
  3. 100 and NULL (correct answer)
  4. NULL only
Explanation: When you see a predicate like IS NOT TRUE in SQL, you need to think carefully about three-valued logic — the fact that SQL operates with TRUE, FALSE, and UNKNOWN (which arises whenever NULL is involved). First, evaluate the inner condition amount > 100 for each row. For 150, this is TRUE. For 100, this is FALSE. For NULL, any comparison with NULL yields UNKNOWN. Now apply IS NOT TRUE to each result: TRUE becomes FALSE (so 150 is excluded), FALSE becomes TRUE (so 100 is kept), and UNKNOWN also becomes TRUE (so NULL is kept). That means 100 and NULL are returned — making C the correct answer. Here's where the wrong answers reveal common traps. A (100 only) ignores NULL's behavior entirely, assuming UNKNOWN behaves like FALSE and gets filtered out — but IS NOT TRUE explicitly catches UNKNOWN. B (150 and NULL) confuses IS NOT TRUE with something like NOT (amount > 100), where a student might think "not greater than 100" means 150 is excluded but NULL is ambiguous — yet 100 would also satisfy NOT TRUE and should be included. D (NULL only) incorrectly treats NULL as the only non-TRUE case, forgetting that a plain FALSE result also passes IS NOT TRUE. The key study tip: always distinguish between NOT (condition) and (condition) IS NOT TRUE. NOT on an UNKNOWN still yields UNKNOWN (and gets filtered out), but IS NOT TRUE explicitly returns TRUE for both FALSE and UNKNOWN results — so NULL rows survive the filter.

Question 11

A table contains these four rows: r1: (a = 1, b = NULL), r2: (a = 0, b = 2), r3: (a = 0, b = NULL), and r4: (a = 0, b = 3).

Which rows are returned by the following standard SQL filter?

WHERE NOT (a = 1 OR b = 2)

  1. r3 and r4 only
  2. r4 only (correct answer)
  3. r1 and r4 only
  4. r2, r3, and r4 only
Explanation: When SQL evaluates a WHERE clause involving NULL, you must apply three-valued logic: expressions can be TRUE, FALSE, or UNKNOWN. A row is only returned if the final condition evaluates to TRUE — not UNKNOWN. Start by applying De Morgan's Law: NOT (a = 1 OR b = 2) is logically equivalent to a ≠ 1 AND b ≠ 2. Now trace each row:
  • r1 (a=1, b=NULL): a ≠ 1 → FALSE. The AND short-circuits to FALSE. Not returned.
  • r2 (a=0, b=2): a ≠ 1 → TRUE, b ≠ 2 → FALSE. AND = FALSE. Not returned.
  • r3 (a=0, b=NULL): a ≠ 1 → TRUE, b ≠ 2 → UNKNOWN (NULL comparisons always yield UNKNOWN). AND = UNKNOWN. Not returned.
  • r4 (a=0, b=3): a ≠ 1 → TRUE, b ≠ 2 → TRUE. AND = TRUE. Returned.
Only r4 passes, confirming B is correct. Choice A incorrectly includes r3. Students often assume r3 should pass because it "doesn't have b = 2," but NULL ≠ 2 is UNKNOWN, not TRUE — so it fails. Choice C incorrectly includes r1, which fails because a = 1 makes the original OR true, so NOT of that is FALSE. Choice D includes r2 and r3, both of which are eliminated for the reasons above. Your study tip: whenever you see NOT applied to an expression involving potential NULLs, always evaluate UNKNOWN explicitly — rows with UNKNOWN results are silently excluded, which is one of SQL's most common exam traps.

Question 12

An audit query compares five (old_value, new_value) pairs: p1 = (5, 5), p2 = (5, 8), p3 = (NULL, 8), p4 = (8, NULL), and p5 = (NULL, NULL).

Which pairs are identified as changed by the filter WHERE old_value <> new_value?

  1. p2 only (correct answer)
  2. p2, p3, and p4 only
  3. p2, p3, p4, and p5
  4. p1 and p5 only
Explanation: Whenever you see a question involving <> (not equal) comparisons in SQL, the most important concept to keep in mind is how SQL handles NULL values. SQL uses three-valued logic: expressions can evaluate to TRUE, FALSE, or UNKNOWN — and NULL is the culprit behind UNKNOWN. The WHERE clause only passes rows where the condition evaluates to TRUE. Any row producing UNKNOWN is silently filtered out, just like FALSE. With that in mind, walk through each pair under WHERE old_value <> new_value:
  • p1 (5, 5): 5 <> 5 → FALSE. Filtered out.
  • p2 (5, 8): 5 <> 8 → TRUE. Passes.
  • p3 (NULL, 8): NULL <> 8 → UNKNOWN. Filtered out.
  • p4 (8, NULL): 8 <> NULL → UNKNOWN. Filtered out.
  • p5 (NULL, NULL): NULL <> NULL → UNKNOWN. Filtered out.
So only p2 is identified as changed, confirming that A is correct. Choice B is wrong because it assumes NULL comparisons produce TRUE when values differ — they don't, they produce UNKNOWN. Choice C makes the same mistake and adds p5, perhaps assuming NULL <> NULL is TRUE. Choice D is a trap that confuses p1 (which correctly evaluates to FALSE) with pairs that "pass," and lumps in p5 incorrectly. Study tip: Any arithmetic or comparison involving NULL produces NULL (UNKNOWN), not TRUE or FALSE. If your audit logic needs to catch NULL-involved changes, use IS DISTINCT FROM or combine IS NULL checks explicitly — standard <> will silently miss them.

Question 13

A developer wants to rewrite NOT (a = 1 AND b = 2) without changing its TRUE, FALSE, or UNKNOWN result for any values of a and b, including NULLs. Which replacement is equivalent under standard SQL three-valued logic?

  1. (a <> 1) OR (b <> 2) (correct answer)
  2. (a <> 1) AND (b <> 2)
  3. (a = 1) OR (b = 2)
  4. (a IS NULL) OR (b IS NULL)
Explanation: Whenever you see a question involving NOT applied to a compound condition, your first instinct should be De Morgan's Laws: NOT (A AND B) becomes (NOT A) OR (NOT B), and NOT (A OR B) becomes (NOT A) AND (NOT B). The critical twist in SQL is that these laws must hold under three-valued logic, where expressions can be TRUE, FALSE, or UNKNOWN (due to NULLs). Applying De Morgan's Law to NOT (a = 1 AND b = 2) gives you (a <> 1) OR (b <> 2), which is answer A. You can verify this with NULLs: if a is NULL, then a = 1 is UNKNOWN, a <> 1 is also UNKNOWN, and the original NOT (UNKNOWN AND ...) behaves identically to UNKNOWN OR (b <> 2) — the truth tables match in every case. Answer B — (a <> 1) AND (b <> 2) — is the De Morgan transformation of NOT (a OR b), not NOT (a AND b). This is the classic trap: students swap AND/OR in the wrong direction. For example, if a = 1 and b = 3, the original expression is TRUE, but B evaluates to FALSE. Answer C — (a = 1) OR (b = 2) — is essentially the un-negated inner expression rearranged, with no negation applied at all. It returns the opposite truth value in many cases. Answer D — (a IS NULL) OR (b IS NULL) — only addresses NULL-specific scenarios and ignores ordinary non-NULL comparisons entirely. Your study tip: memorize De Morgan's Laws as a pair — NOT (A AND B) = (NOT A) OR (NOT B) — and remember that SQL NULLs don't break these laws, they just propagate UNKNOWN consistently through them.

Question 14

In standard SQL, a table has the constraint CHECK (salary > 0) but does not have a NOT NULL constraint on salary. Four separate inserts attempt to store NULL, 0, -5, and 10.

Assuming no other constraints apply, which salary values pass the CHECK constraint?

  1. NULL and 10 (correct answer)
  2. 0 and 10
  3. 10 only
  4. NULL, 0, and 10
Explanation: When working with SQL CHECK constraints, the key concept to understand is how SQL handles NULL values in Boolean expressions — and it behaves differently than you might expect. SQL uses three-valued logic: an expression can evaluate to TRUE, FALSE, or UNKNOWN. A CHECK constraint only rejects a row when the condition evaluates to FALSE. If it evaluates to TRUE or UNKNOWN, the row is accepted. Now apply this to CHECK (salary > 0) for each value:
  • NULL: NULL > 0 evaluates to UNKNOWN (any comparison with NULL yields UNKNOWN). Since the result isn't FALSE, the row passes.
  • 0: 0 > 0 evaluates to FALSE. The row is rejected.
  • -5: -5 > 0 evaluates to FALSE. The row is rejected.
  • 10: 10 > 0 evaluates to TRUE. The row passes.
So NULL and 10 both pass — making A the correct answer. Answer B is wrong because 0 > 0 is clearly FALSE, so 0 is rejected by the constraint. Answer C is a trap for students who know 10 works but forget SQL's three-valued logic and assume NULL would also be blocked. Answer D incorrectly includes 0 and misunderstands how NULL behaves, treating UNKNOWN as TRUE across the board. A useful study tip: remember the phrase "UNKNOWN is not FALSE." In SQL, CHECK constraints only block rows on a definitive FALSE — NULL comparisons slip through unless you also add a NOT NULL constraint. Watch for questions that test both constraints together.

Question 15

A query evaluates the following expression for two rows, one with code = NULL and another with code = 7:

CASE WHEN code = NULL THEN 'A' WHEN code IS NULL THEN 'B' ELSE 'C' END

What results are produced for code = NULL and code = 7, respectively?

  1. 'A' and 'C', respectively
  2. 'B' and 'C', respectively (correct answer)
  3. 'B' and 'A', respectively
  4. 'C' and 'C', respectively
Explanation: Whenever you see a CASE expression involving NULL, the critical concept to remember is how SQL handles NULL comparisons. In SQL, NULL is never equal to anything — not even itself. Any equality check like code = NULL always evaluates to UNKNOWN (not TRUE, not FALSE), which means a CASE WHEN clause using = will never match a NULL value. The only reliable way to check for NULL is with IS NULL or IS NOT NULL. With that in mind, trace through both rows. For code = NULL: the first condition code = NULL evaluates to UNKNOWN (not TRUE), so it's skipped. The second condition code IS NULL evaluates to TRUE, so the result is 'B'. For code = 7: neither code = NULL (UNKNOWN) nor code IS NULL (FALSE) matches, so execution falls through to ELSE, returning 'C'. That gives you 'B' and 'C' — confirming B is correct. A is wrong because it assumes code = NULL successfully matches a NULL value, which it never does in SQL. C is wrong for the same reason regarding the first row, and also incorrectly gives 'A' for code = 7 when no condition matches 7. D is a tempting trap — it correctly recognizes that code = NULL fails, but then incorrectly concludes the second WHEN clause also fails, when in fact code IS NULL is perfectly valid and returns TRUE for a NULL value. Your takeaway: any time you see = NULL in SQL, treat it as a bug — always use IS NULL to test for null values, and expect = NULL comparisons to silently fall through in CASE expressions.