SQL Quiz: Is Null Is Not Null
10 questions · exam conditions
0:00
Is Null Is Not NullQuestion 1 of 10

An evaluations table contains these department and rating pairs: department A has ratings NULL and NULL; department B has ratings NULL and 4; department C has ratings 3 and 5; department D has one rating, which is NULL.

Which departments are returned by this query?

SELECT department FROM evaluations GROUP BY department HAVING MAX(rating) IS NULL ORDER BY department;

Department D only
Departments A, B, and D
Departments B and C
Departments A and D
← Back to quizzes

SQL Quiz

SQL Quiz: Is Null Is Not Null

Practice Is Null Is Not 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 Is Null Is Not 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

An evaluations table contains these department and rating pairs: department A has ratings NULL and NULL; department B has ratings NULL and 4; department C has ratings 3 and 5; department D has one rating, which is NULL.

Which departments are returned by this query?

SELECT department FROM evaluations GROUP BY department HAVING MAX(rating) IS NULL ORDER BY department;

  1. Department D only
  2. Departments A, B, and D
  3. Departments B and C
  4. Departments A and D (correct answer)
Explanation: When working with HAVING and aggregate functions, you need to carefully think about what value the aggregate actually produces — especially when NULLs are involved. MAX(rating) scans all ratings within each department group and returns the highest non-NULL value. If every rating in a group is NULL, there are no non-NULL values to compare, so MAX(rating) itself returns NULL. The HAVING MAX(rating) IS NULL clause then filters for only those departments where this is the case. Let's walk through each department: Department A has ratings NULL and NULL — MAX(rating) returns NULL ✓. Department B has ratings NULL and 4 — MAX(rating) returns 4, not NULL ✗. Department C has ratings 3 and 5 — MAX(rating) returns 5, not NULL ✗. Department D has one rating of NULL — MAX(rating) returns NULL ✓. So only departments A and D pass the HAVING filter, making D the correct answer. Choice A is wrong because it omits department A, which also has all-NULL ratings and therefore a NULL MAX. Choice B incorrectly includes department B — because B has a non-NULL rating of 4, its MAX is 4, not NULL. Choice C is entirely wrong; departments B and C both have non-NULL ratings, giving them concrete MAX values that fail the IS NULL check. A key pattern to remember: aggregate functions like MAX, MIN, SUM, and AVG ignore NULLs during calculation. They only return NULL themselves when the entire input set consists of NULLs. Watch for this distinction whenever you see aggregates combined with IS NULL checks in HAVING clauses.

Question 2

Two staging tables, source_rows s and target_rows t, each contain a nullable external_code. Rows should match when the codes are equal or when both codes are NULL.

Which join condition implements the required matching rule using standard NULL predicates?

  1. s.external_code = t.external_code OR s.external_code IS NULL
  2. s.external_code = t.external_code OR t.external_code IS NULL
  3. s.external_code = t.external_code OR (s.external_code IS NULL AND t.external_code IS NULL) (correct answer)
  4. s.external_code = t.external_code AND (s.external_code IS NOT NULL OR t.external_code IS NOT NULL)
Explanation: When working with nullable columns in SQL, you need to remember a fundamental rule: NULL does not equal NULL. The expression NULL = NULL evaluates to UNKNOWN, not TRUE, so a simple equality check will silently drop rows where both sides are NULL. This question tests whether you can construct a condition that handles both the equal-values case and the both-NULL case explicitly. The correct answer is C because it covers exactly the two scenarios described: either both codes are non-NULL and equal (s.external_code = t.external_code), or both codes are NULL (s.external_code IS NULL AND t.external_code IS NULL). The OR between them ensures a match fires in either situation, and the AND inside the second clause ensures both sides must be NULL — not just one. Answer A fails because it matches any source row where s.external_code IS NULL regardless of what t.external_code contains — including non-NULL values. This produces false matches. Answer B has the mirror problem: it matches any target row where t.external_code IS NULL, again ignoring whether the source is also NULL. Both A and B are one-sided NULL checks that over-match. Answer D moves in the wrong direction entirely — the AND with a NOT NULL guard would exclude the very rows where both codes are NULL, which is the opposite of what the requirement asks for. A handy rule to remember: whenever a spec says "treat NULLs as equal to each other," you need (col_a IS NULL AND col_b IS NULL) as an explicit branch. Never assume equality handles it — SQL equality and NULL simply don't mix.

Question 3

A shipments table contains these rows, shown as (shipment_id, delivered_at, returned_at): (1, NULL, NULL), (2, '2026-05-01', NULL), (3, NULL, '2026-05-03'), (4, '2026-05-02', '2026-05-06'), and (5, NULL, NULL).

Which shipment IDs are returned by the following query?

SELECT shipment_id FROM shipments WHERE delivered_at IS NULL OR returned_at IS NOT NULL ORDER BY shipment_id;

  1. Shipment IDs 1, 3, 4, and 5 (correct answer)
  2. Shipment IDs 1, 2, 3, and 5
  3. Shipment IDs 3 and 4 only
  4. Shipment IDs 1, 3, and 5 only
Explanation: When working with NULL values in SQL, you need to understand two key concepts: how NULL comparisons work, and how OR combines conditions. NULL represents an unknown value, so you can never use = NULL — you must use IS NULL or IS NOT NULL. The WHERE clause here returns any row where either condition is true: delivered_at IS NULL or returned_at IS NOT NULL. Walk through each row:
  • Shipment 1: delivered_at is NULL ✓ → included
  • Shipment 2: delivered_at is not NULL, returned_at is NULL (not NOT NULL) → excluded
  • Shipment 3: delivered_at is NULL ✓ → included
  • Shipment 4: delivered_at is not NULL, but returned_at IS NOT NULL ✓ → included
  • Shipment 5: delivered_at is NULL ✓ → included
This gives you shipments 1, 3, 4, and 5 — confirming A is correct. B is wrong because it includes shipment 2 (which has a non-NULL delivered_at and a NULL returned_at, failing both conditions) and excludes shipment 4. C is wrong because it only includes rows where both conditions might seem true together, confusing OR with AND. D is the trickiest trap — it correctly excludes shipment 2 but wrongly drops shipment 4, likely from overlooking that returned_at IS NOT NULL independently satisfies the OR. Study tip: With OR, a row only needs one condition to be true. Always trace each row individually against each branch of the condition, especially when NULLs are involved.

Question 4

The tasks table has five rows. Their assignee_id values are, in order: NULL, 12, NULL, 18, and NULL.

What values are returned as row_count and assignee_count by this query?

SELECT COUNT(*) AS row_count, COUNT(assignee_id) AS assignee_count FROM tasks WHERE assignee_id IS NULL;

  1. row_count is 2, and assignee_count is 0
  2. row_count is 3, and assignee_count is 3
  3. row_count is 5, and assignee_count is 2
  4. row_count is 3, and assignee_count is 0 (correct answer)
Explanation: When working with COUNT in SQL, the most important distinction to internalize is that COUNT(*) counts rows, while COUNT(column_name) counts non-NULL values in that column. Layer that with WHERE clause filtering, and this question becomes a two-step problem. The WHERE assignee_id IS NULL clause filters the table first, keeping only the three rows where assignee_id is NULL (positions 1, 3, and 5). All subsequent counting happens on this filtered result set of three rows — not the original five. So COUNT(*) counts those three rows, giving row_count = 3. Then COUNT(assignee_id) counts non-NULL values of assignee_id among those same three rows — but since every row that passed the filter has a NULL assignee_id by definition, there are zero non-NULL values to count, giving assignee_count = 0. That confirms D is correct. A is wrong because it reports row_count = 2, which would correspond to the non-NULL rows (12 and 18) — essentially confusing which rows the WHERE clause keeps. B is wrong because assignee_count = 3 would only be true if the filtered rows had non-NULL assignee_id values, which contradicts the filter itself. C is wrong because it ignores the WHERE clause entirely, reporting counts from the full five-row table. The key trap here is forgetting that WHERE filters happen before COUNT aggregates. Always mentally apply your WHERE clause first, then ask what COUNT(*) vs. COUNT(column) does to that reduced dataset.

Question 5

The requests.status column may contain 'APPROVED', 'REJECTED', 'PENDING', or NULL. NULL means that no status has been assigned.

Which predicate returns every request that is not currently approved, including requests with no assigned status?

  1. status <> 'APPROVED' OR status IS NULL (correct answer)
  2. status <> 'APPROVED' AND status IS NOT NULL
  3. NOT (status = 'APPROVED') AND status IS NULL
  4. status = 'REJECTED' OR status = 'PENDING'
Explanation: Whenever you work with nullable columns in SQL, you need to remember one critical rule: NULL never equals anything, and NULL never not-equals anything. Comparisons like status <> 'APPROVED' silently exclude NULL rows because NULL produces an unknown result, not TRUE or FALSE. To capture every request that isn't approved — including those with no status at all — you need two conditions joined with OR: check that the status differs from 'APPROVED', and separately catch the NULLs that slip through. That's exactly what A does. status <> 'APPROVED' catches 'REJECTED' and 'PENDING', while status IS NULL explicitly grabs the unassigned rows. Together, they cover the entire "not approved" universe. B fails because it uses AND with status IS NOT NULL, which actively excludes NULL rows — the opposite of what you want. C combines NOT (status = 'APPROVED') with AND status IS NULL, so it only returns NULL rows, completely missing 'REJECTED' and 'PENDING'. It's both overly restrictive and logically confused. D hardcodes only two status values, meaning it breaks the moment a new status is added and still misses NULL rows entirely — a fragile, incomplete approach. A practical tip to remember: whenever you write <> 'something' on a nullable column, always ask yourself, "Do I also need the NULLs?" If yes, append OR column IS NULL. Think of NULLs as invisible rows that standard comparisons simply walk past — you have to call them out by name with IS NULL.

Question 6

A report accepts a parameter :department_id. If the parameter is NULL, the report must return employees from every department. If it is non-NULL, the report must return only employees whose department_id equals the parameter. Employees whose own department_id is NULL do not match a non-NULL parameter.

Which WHERE clause correctly implements the optional parameter?

  1. WHERE department_id = :department_id OR department_id IS NULL
  2. WHERE :department_id IS NULL OR department_id = :department_id (correct answer)
  3. WHERE :department_id IS NOT NULL OR department_id = :department_id
  4. WHERE department_id IS NULL AND :department_id IS NULL
Explanation: When building an optional filter parameter in SQL, the key pattern to master is short-circuit evaluation: SQL evaluates OR conditions left to right, and if the first condition is true, the second is never checked. This is exactly the logic you need for an optional parameter. The requirement has two modes: when :department_id IS NULL, all rows should pass through (no filtering); when it's non-NULL, only matching rows should appear. Option B — WHERE :department_id IS NULL OR department_id = :department_id — nails this perfectly. If the parameter is NULL, the first condition is true and every row passes, regardless of the employee's department_id. If the parameter is non-NULL, the first condition is false, so SQL evaluates the second condition and filters to exact matches only. Employees with a NULL department_id won't satisfy department_id = :department_id (because NULL comparisons always yield unknown), which aligns with the stated requirement. Option A fails because even when a non-NULL parameter is provided, any employee with a NULL department_id still slips through via the OR department_id IS NULL branch — that's the opposite of what's required. Option C uses IS NOT NULL, which inverts the logic entirely: it forces filtering off when the parameter has a value, meaning rows would pass freely when you actually want to filter them. Option D is far too restrictive — it only returns rows where both the employee's department and the parameter are NULL, excluding all real department data. A useful mental shortcut: always write the "bypass" condition first (parameter IS NULL), then chain your actual filter with OR. This pattern appears frequently in reporting queries and stored procedures.

Question 7

The customers table has a non-NULL primary key customer_id. The orders table has a non-NULL primary key order_id and contains customer_id and status. A customer can have completed, pending, or no orders.

Which query returns every customer who has no completed order, including customers who have no orders at all?

  1. SELECT c.customer_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status <> 'COMPLETED' OR o.status IS NULL;
  2. SELECT c.customer_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'COMPLETED' WHERE o.order_id IS NULL; (correct answer)
  3. SELECT c.customer_id FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'COMPLETED' WHERE o.order_id IS NOT NULL;
  4. SELECT c.customer_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status IS NULL OR o.order_id IS NOT NULL;
Explanation: When filtering rows after a JOIN, the placement of your condition matters enormously — it changes whether the condition acts as a filter or as a join criterion. This question tests exactly that distinction. The goal is to find customers with zero completed orders, including those with no orders at all. The right approach is to use a LEFT JOIN with the completed-order condition inside the ON clause, then check for no match in the WHERE clause. That's precisely what B does: it joins orders only when status = 'COMPLETED', so customers with no completed orders produce a NULL order_id on the right side. The WHERE clause then keeps only those unmatched rows. Customers with no orders at all also produce NULLs and pass through correctly. A is a common trap. It uses a LEFT JOIN but moves the condition to the WHERE clause. After the join, every order row exists — including pending ones — so a customer with only pending orders satisfies o.status <> 'COMPLETED' and appears in results correctly, but a customer with both completed and pending orders would still appear (because the pending rows survive). More critically, the logic doesn't reliably exclude customers who have any completed order. C uses an INNER JOIN, which immediately drops customers with no orders at all — the opposite of what you want. D looks plausible but its WHERE condition (status IS NULL OR order_id IS NOT NULL) would actually include rows where an order exists regardless of status, pulling in completed orders and defeating the purpose. The strategy to remember: put selective join conditions in the ON clause, and use a NULL check in the WHERE clause to find "anti-matches" in LEFT JOINs.

Question 8

A query must return products whose non-NULL category_id is not blocked. The blocked_categories table may itself contain rows where category_id is NULL. The current predicate is p.category_id NOT IN (SELECT category_id FROM blocked_categories).

Which modification prevents a NULL in blocked_categories from causing the NOT IN predicate to reject all candidate products?

  1. p.category_id NOT IN (SELECT category_id FROM blocked_categories WHERE category_id IS NOT NULL) (correct answer)
  2. p.category_id IS NOT NULL AND p.category_id NOT IN (SELECT category_id FROM blocked_categories)
  3. p.category_id NOT IN (SELECT category_id FROM blocked_categories) OR p.category_id IS NULL
  4. p.category_id NOT IN (SELECT category_id FROM blocked_categories WHERE category_id IS NULL)
Explanation: Whenever you work with NOT IN and a subquery, you must think about three-valued logic (TRUE, FALSE, UNKNOWN). In SQL, comparing any value to NULL produces UNKNOWN, not FALSE. Because NOT IN internally evaluates as a series of <> NULL comparisons, a single NULL in the subquery's result set makes the entire NOT IN expression return UNKNOWN for every candidate row — which means no rows pass the filter. This is the silent killer of many SQL queries. The fix in A is the cleanest and most direct: by adding WHERE category_id IS NOT NULL inside the subquery, you scrub NULLs from the list before the comparison even begins. The NOT IN predicate then only compares against actual category values, so it behaves exactly as you'd expect. B is a red herring that feels protective but isn't. It guards against p.category_id being NULL (which the problem already says it won't be), but it does nothing about NULLs inside the subquery — those still poison the result set. C tries to recover rows by ORing in an IS NULL check on p.category_id. Since the problem states p.category_id is non-NULL, this clause never activates and the NULL-in-subquery problem remains unsolved. D is logically backwards — it filters the subquery to keep only NULL rows, guaranteeing that the NOT IN check still faces a NULL and breaks. The takeaway: whenever you write NOT IN (subquery), immediately ask yourself whether the subquery could return NULLs, and defensively add WHERE column IS NOT NULL inside it.

Question 9

A contacts table contains these rows, shown as (contact_id, email, phone, active_flag): (1, NULL, NULL, 'Y'), (2, NULL, '555-0102', 'N'), (3, 'c@example.com', NULL, 'Y'), (4, 'd@example.com', '555-0104', 'Y'), and (5, NULL, '555-0105', 'Y').

Which contact IDs are returned by this query?

SELECT contact_id FROM contacts WHERE email IS NULL OR phone IS NULL AND active_flag = 'Y' ORDER BY contact_id;

  1. Contact IDs 1, 3, and 5
  2. Contact IDs 1, 2, 3, and 5 (correct answer)
  3. Contact IDs 1, 2, and 5
  4. Contact IDs 1, 3, 4, and 5
Explanation: Whenever you see a compound WHERE clause mixing OR and AND, your first job is to apply operator precedence: AND is evaluated before OR, just like multiplication before addition in arithmetic. Ignoring this rule is the most common trap in these questions. The query's WHERE clause is: email IS NULL OR phone IS NULL AND active_flag = 'Y'. Because AND binds tighter, SQL reads this as email IS NULL OR (phone IS NULL AND active_flag = 'Y'). Now evaluate each row:
  • Row 1: email IS NULL → TRUE. Included.
  • Row 2: email IS NULL → TRUE. Included (even though active_flag = 'N', the OR short-circuits).
  • Row 3: email is not NULL, so check right side: phone IS NULL AND active_flag = 'Y' → TRUE. Included.
  • Row 4: email is not NULL; phone is not NULL → right side FALSE. Excluded.
  • Row 5: email is not NULL; phone IS NULL AND active_flag = 'Y' → TRUE. Included.
That gives contact IDs 1, 2, 3, and 5 — confirming B is correct. A (1, 3, 5) drops row 2, which is the error of applying active_flag = 'Y' to the entire expression rather than just the AND branch. C (1, 2, 5) drops row 3, incorrectly requiring phone IS NULL when email already satisfies the OR. D (1, 3, 4, 5) includes row 4, which fails both sides of the OR. As a study tip: whenever you see OR and AND together without parentheses, mentally add parentheses around the AND clause first — it will save you from the most frequent SQL logic mistake on exams.

Question 10

A report must return rows only when both reviewed_at and approved_at contain non-NULL values. Which predicate is logically equivalent to NOT (reviewed_at IS NULL OR approved_at IS NULL)?

  1. reviewed_at IS NOT NULL OR approved_at IS NOT NULL
  2. reviewed_at IS NULL AND approved_at IS NULL
  3. reviewed_at IS NOT NULL AND approved_at IS NOT NULL (correct answer)
  4. reviewed_at IS NULL OR approved_at IS NULL
Explanation: When you see a negation applied to a logical expression in SQL, your first instinct should be De Morgan's Laws: negating an OR turns it into an AND (and vice versa), while each individual condition also flips. This is exactly what's being tested here. Starting with NOT (reviewed_at IS NULL OR approved_at IS NULL), apply De Morgan's Law: the OR becomes AND, and each condition negates individually. IS NULL negated becomes IS NOT NULL, giving you reviewed_at IS NOT NULL AND approved_at IS NOT NULL. That's answer C — both columns must have values for the row to appear, which matches the report's requirement perfectly. Looking at the distractors: A (IS NOT NULL OR IS NOT NULL) is a common trap — it flips the NULL checks correctly but changes AND to OR instead of keeping it as AND. This would return rows where at least one column is non-NULL, not both. B (IS NULL AND IS NULL) goes in the completely wrong direction — it requires both columns to be NULL, which is the opposite of what the report needs. D is just the original inner expression without the negation applied at all, meaning it returns rows where at least one date is missing — again, the opposite of the intent. A handy memory device for De Morgan's Laws: "Break the line, change the sign." When you push a NOT inside parentheses, the connector (AND/OR) flips, and each individual predicate negates. Keep this rule on hand whenever you encounter NOT (condition1 OR condition2) or NOT (condition1 AND condition2) in SQL filter logic.