SQL Quiz: Null And Missingness
10 questions · exam conditions
0:00
Null And MissingnessQuestion 1 of 10

A child table has a nullable single-column foreign key parent_id referencing the non-NULL primary key of a parent table. No parent row has an identifier equal to a proposed child value of NULL.

Under standard SQL's ordinary single-column foreign-key behavior, what happens when that child row is inserted?

The insert succeeds because a NULL foreign-key value does not require a matching parent
The insert fails because every child row must identify an existing parent row
The insert succeeds only if the parent table contains exactly one row with a NULL identifier
The insert fails because a foreign-key declaration implicitly makes the child column non-NULL
← Back to quizzes

SQL Quiz

SQL Quiz: Null And Missingness

Practice Null And Missingness 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 Null And Missingness, 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

A child table has a nullable single-column foreign key parent_id referencing the non-NULL primary key of a parent table. No parent row has an identifier equal to a proposed child value of NULL.

Under standard SQL's ordinary single-column foreign-key behavior, what happens when that child row is inserted?

  1. The insert succeeds because a NULL foreign-key value does not require a matching parent (correct answer)
  2. The insert fails because every child row must identify an existing parent row
  3. The insert succeeds only if the parent table contains exactly one row with a NULL identifier
  4. The insert fails because a foreign-key declaration implicitly makes the child column non-NULL
Explanation: When working with foreign keys in SQL, the most important concept to internalize is the difference between a missing value and an invalid value. A NULL in a foreign key column doesn't mean "no matching parent exists" — it means "the relationship is unknown or not applicable." Standard SQL treats these two situations very differently. Because of this distinction, A is correct. SQL's foreign-key constraint says a non-NULL child value must match an existing parent key. When the child column contains NULL, the constraint simply doesn't fire — the database skips the referential check entirely. This behavior is defined in the SQL standard and is implemented consistently across major databases like PostgreSQL, MySQL, and SQL Server. B is wrong because it overstates the constraint's reach. Foreign keys only enforce that known parent references are valid; they make no demand about rows where the relationship is intentionally absent (NULL). C introduces a false condition — the parent table's contents are completely irrelevant when the child value is NULL, regardless of whether the parent has zero rows or a million. D is wrong because a foreign-key declaration carries no implicit NOT NULL constraint. Making a column non-nullable requires an explicit NOT NULL definition; the two concepts are entirely independent. A useful memory anchor: think of NULL as "opting out" of the foreign-key check. The constraint only activates for concrete values. On exam questions involving NULLs and constraints, always ask yourself whether the rule in question applies to unknowns — most integrity constraints in SQL are designed to ignore NULL rather than reject it.

Question 2

A developer joins two customer-import tables using this condition:

COALESCE(a.email, 'unknown') = COALESCE(b.email, 'unknown')

Each table contains several rows whose email is NULL, and 'unknown' is not a real email address.

What is the main missingness-related risk of this join condition?

  1. Rows with one NULL email and one known email will match because COALESCE ignores both values
  2. Rows with known equal emails will fail to match because COALESCE always returns its fallback
  3. Rows with NULL emails on both sides will match even though their actual emails are not known to be equal (correct answer)
  4. Rows with NULL emails will be removed before the join condition can evaluate either expression
Explanation: When working with NULL values in SQL join conditions, the key question to ask is: what happens when a substitution value is the same for multiple unrelated rows? That's exactly the trap being tested here. COALESCE(a.email, 'unknown') returns the email if it exists, or 'unknown' if it's NULL. This means every row with a NULL email gets replaced by the identical string 'unknown'. When you join on this expression, any NULL-email row in table a will match every NULL-email row in table b — because both sides evaluate to 'unknown'. In reality, those NULLs represent unknown email addresses that may belong to completely different people. The join silently treats them as identical, producing false matches and inflating your result set. That's why C is correct. A is wrong because COALESCE doesn't "ignore" values — it actively returns the first non-NULL one. A row with one NULL and one known email would produce different strings (e.g., 'unknown' vs 'alice@example.com'), so they would not match. B is wrong because COALESCE only falls back to 'unknown' when the email is NULL. If both rows have the same known email, both sides return that email, and the match succeeds normally. D is wrong because COALESCE doesn't filter rows — it's a scalar function that transforms values in place. NULL rows are fully present during the join evaluation. As a study tip: whenever you see a NULL-substitution pattern in a join condition, ask yourself whether that placeholder value could create unintended collisions across unrelated rows.

Question 3

A table Items contains item codes 1, 2, and 3. A subquery against BlockedItems returns two values: 2 and NULL.

Under standard SQL three-valued logic, what does this query return?

SELECT item_code FROM Items WHERE item_code NOT IN (SELECT item_code FROM BlockedItems);

  1. Item codes 1 and 3
  2. Item code 2 only
  3. No item codes (correct answer)
  4. All three item codes
Explanation: Whenever you see NOT IN paired with a subquery, your first instinct should be to check whether that subquery can return NULL. This question is testing your understanding of SQL's three-valued logic, where comparisons don't just produce TRUE or FALSE — they can also produce UNKNOWN. Here's what happens under the hood: NOT IN (2, NULL) expands into a series of comparisons. For item code 1, SQL evaluates 1 NOT IN (2, NULL), which means 1 <> 2 AND 1 <> NULL. That second comparison — 1 <> NULL — produces UNKNOWN, not TRUE. Because TRUE AND UNKNOWN is UNKNOWN, the entire condition is UNKNOWN, and the row is filtered out. The same logic applies to item code 3. Even item code 2 is excluded because 2 = 2 makes the IN check TRUE, so NOT IN is FALSE. No rows survive — meaning C is correct. Answer A is the intuitive trap: you might assume that 1 and 3 aren't blocked and should pass through. That reasoning ignores the NULL contamination problem entirely. Answer B is wrong because item code 2 actually matches a blocked value, so it would never appear in a NOT IN result regardless of the NULL issue. Answer D would only be correct if the subquery returned an empty set — a non-empty subquery containing NULL always kills NOT IN results. Your study tip: NOT IN + NULL = empty result set. Whenever a subquery might return NULLs, use NOT EXISTS instead — it handles NULLs safely and is a reliable pattern to prefer in practice.

Question 4

An application uses the following optional-filter pattern, where :p is a parameter:

WHERE department_id = :p OR :p IS NULL

The department_id column itself is nullable.

If the application binds NULL to :p, which rows pass the predicate?

  1. Only rows whose department_id is NULL
  2. Only rows whose department_id is non-NULL
  3. No rows, because equality involving the parameter is UNKNOWN
  4. All rows, including rows whose department_id is NULL (correct answer)
Explanation: When you see an OR condition in SQL, remember that the entire predicate is TRUE if either side evaluates to TRUE — and that short-circuit logic is the key to unlocking this question. When :p is bound to NULL, evaluate each side of OR separately. The left side, department_id = NULL, uses equality with NULL, which always produces UNKNOWN — never TRUE, never FALSE. So far, no rows pass on that side alone. But the right side, :p IS NULL, asks "is NULL null?" — and the answer is unambiguously TRUE. Because IS NULL is specifically designed to test for nullness without the three-valued logic problem, it returns TRUE for every row. Since TRUE OR UNKNOWN = TRUE, every single row passes the predicate, making D the correct answer. Choice A is wrong because department_id = NULL never returns TRUE — even for rows where department_id actually is NULL. Equality cannot detect NULL; only IS NULL can. Choice B is equally wrong for the same reason: the equality comparison fails for all rows, not just null ones. Choice C reflects a very common misconception — that UNKNOWN on the left side "poisons" the whole expression. It doesn't, because TRUE OR UNKNOWN resolves to TRUE, not UNKNOWN. The optional-filter pattern (col = :p OR :p IS NULL) is a deliberate design technique: when the parameter is NULL, the filter is bypassed entirely and all rows are returned. This is intentional behavior for building flexible search queries. Study tip: Always evaluate each branch of an OR independently — a TRUE on any branch wins, regardless of UNKNOWN elsewhere.

Question 5

A table contains three rows in its nullable region column: NULL, NULL, and 'East'. A query groups by region and selects region, COUNT(*), and COUNT(region).

Which description of the grouped results is correct?

  1. There are two groups; the NULL group has counts 2 and 0, while East has counts 1 and 1 (correct answer)
  2. There are three groups; each NULL forms a separate group, and every group has both counts equal to 1
  3. There are two groups; the NULL group has both counts equal to 2, while East has both equal to 1
  4. There is one group for East; rows with a NULL grouping value are omitted before aggregation
Explanation: When you see a question combining GROUP BY on a nullable column with both COUNT(*) and COUNT(column), you need to keep two rules straight: how SQL handles NULLs in grouping, and how the two COUNT variants differ. First, grouping: SQL does group NULLs together. All NULL values in a GROUP BY column collapse into a single group — they are not excluded, nor does each NULL form its own group. So your three rows (NULL, NULL, 'East') produce exactly two groups: one NULL group (2 rows) and one East group (1 row). Second, the COUNT difference: COUNT(*) counts every row in the group regardless of content, while COUNT(region) counts only non-NULL values in that column. For the NULL group, both rows have region = NULL, so COUNT(*) returns 2 but COUNT(region) returns 0 — it skips NULLs. For East, both counts return 1 because the single row has a non-NULL value. This makes A the correct answer. Choice B is wrong on two fronts: NULLs do group together (not separately), and the counts would not all equal 1. Choice C gets the grouping right but incorrectly claims COUNT(region) equals 2 for the NULL group — that would require COUNT(region) to count NULLs, which it never does. Choice D reflects a common misconception that NULLs are silently dropped before aggregation; they aren't — they form their own group. Study tip: Always ask two questions: "Does this aggregate function ignore NULLs?" (most do, including COUNT(col)) and "Does GROUP BY exclude NULLs?" (it does not — they group together).

Question 6

A table Accounts contains four rows: account 1 has balance = 8, account 2 has balance = 0, account 3 has balance = NULL, and account 4 has balance = -2.

Which accounts are returned by the following standard SQL predicate?

WHERE balance > 0 OR NOT (balance > 0)

  1. Accounts 1, 2, 3, and 4
  2. Accounts 1, 2, and 4 only (correct answer)
  3. Account 1 only
  4. Accounts 2, 3, and 4 only
Explanation: When you see a SQL predicate involving OR NOT, the critical concept to apply is three-valued logic (3VL). In SQL, a comparison involving NULL doesn't return TRUE or FALSE — it returns UNKNOWN. This changes how logical operators behave in ways that feel counterintuitive. Let's evaluate WHERE balance > 0 OR NOT (balance > 0) for each account:
  • Account 1 (balance = 8): 8 > 0 is TRUE. TRUE OR NOT TRUE = TRUE OR FALSE = TRUE ✓
  • Account 2 (balance = 0): 0 > 0 is FALSE. FALSE OR NOT FALSE = FALSE OR TRUE = TRUE ✓
  • Account 3 (balance = NULL): NULL > 0 is UNKNOWN. NOT UNKNOWN is also UNKNOWN. So UNKNOWN OR UNKNOWN = UNKNOWN — the row is excluded.
  • Account 4 (balance = -2): -2 > 0 is FALSE. FALSE OR TRUE = TRUE ✓
This confirms B — only accounts 1, 2, and 4 are returned. A is wrong because it assumes the expression is a tautology (always TRUE for every row), which would be the case in classical two-valued logic. But NULL breaks that symmetry. C incorrectly keeps only account 1, as if only balance > 0 were evaluated. D appears to invert the result set, confusing which rows pass versus fail. The key study tip: never assume X OR NOT X is always TRUE in SQL. When X is UNKNOWN, NOT X is also UNKNOWN, and UNKNOWN OR UNKNOWN stays UNKNOWN — meaning NULL-valued rows silently disappear from your results.

Question 7

A table is created with the standard SQL constraint CHECK (discount >= 0 AND discount <= 100), but the discount column has no NOT NULL constraint.

An insertion supplies NULL for discount. What is the most accurate outcome and interpretation?

  1. The row is rejected because every comparison with NULL evaluates to FALSE
  2. The row is accepted because the check is UNKNOWN, so NOT NULL is also needed (correct answer)
  3. The row is accepted because SQL automatically converts NULL to a zero discount
  4. The row is rejected because a bounded range check implicitly makes the column mandatory
Explanation: When working with SQL's CHECK constraints, the key concept to understand is three-valued logic: SQL expressions don't just evaluate to TRUE or FALSE — they can also evaluate to UNKNOWN whenever NULL is involved. Here's what actually happens: the constraint CHECK (discount >= 0 AND discount <= 100) is evaluated when a row is inserted. When discount is NULL, both comparisons (NULL >= 0 and NULL <= 100) evaluate to UNKNOWN, making the entire expression UNKNOWN. SQL's rule is that a CHECK constraint only rejects a row when the condition evaluates to FALSE — if the result is UNKNOWN, the row passes through. This means B is correct: the insertion succeeds, and if you want to prevent NULLs, you must add a separate NOT NULL constraint. A is wrong because it mischaracterizes how NULL comparisons work. NULL comparisons don't produce FALSE — they produce UNKNOWN, which is a critically different outcome that allows the row to be accepted rather than rejected. C is wrong because SQL never silently converts NULL to a default value during constraint evaluation. That kind of automatic conversion doesn't exist unless you explicitly define a DEFAULT clause. D is wrong because there's no such thing as an "implicit mandatory" rule for bounded range checks. A CHECK constraint says nothing about whether a value must be present — only about what values are acceptable if present. A useful rule of thumb: UNKNOWN is not FALSE in SQL. Whenever you see a CHECK constraint, ask yourself what happens when the column is NULL — the answer is almost always "the row sneaks through."

Question 8

A medical database stores a nullable treatment_end_date. A NULL currently means either that treatment is still ongoing or that the patient never began treatment. Reports must distinguish these two situations reliably.

Which design best preserves the distinct meanings of the missing date?

  1. Use one conventional early date (e.g., 1900-01-01) for never started and NULL for ongoing treatment, relying on application code to interpret the sentinel
  2. Keep only the nullable date column and allow each report to infer the reason from surrounding context or business rules
  3. Add a constrained treatment-status or missing-reason attribute alongside the nullable date, and use consistency rules to govern when the date may be NULL (correct answer)
  4. Replace every missing date with the report execution date before storing the row, so no NULLs appear in the table
Explanation: When a single column carries multiple distinct meanings for its NULL values, you've identified a data modeling problem: the column alone cannot communicate which meaning applies. This is the core concept being tested — how to preserve semantic clarity when a missing value has more than one interpretation. The right approach, answer C, is to add a separate treatment_status or missing_reason column alongside the nullable date. This column explicitly captures why the date is absent — for example, 'ONGOING' vs 'NEVER_STARTED'. Pairing it with a consistency constraint (e.g., a CHECK or application-level rule ensuring the date is NULL only when status is one of those two values) makes the design self-documenting and queryable without guesswork. Answer A introduces a sentinel value (1900-01-01), which is a classic antipattern. Sentinel values pollute real data ranges, break aggregations like MIN/MAX, and push interpretation logic into every application that queries the table — a fragile, error-prone arrangement. Answer B relies on "surrounding context or business rules" to infer meaning at report time. This is exactly the ambiguity you're trying to eliminate. Different reports may interpret the NULL differently, leading to inconsistent results and silent bugs. Answer D overwrites missing dates with the report execution date, destroying the information entirely. Now you can't distinguish missing from actually recorded — you've made the data actively misleading. Study tip: When you see a question about NULLs with multiple meanings, ask yourself: "Can a single column reliably communicate all these states?" If not, the answer almost always involves adding an explicit status column with enforced constraints.

Question 9

A nullable decimal column amount contains 10.0, NULL, 20.0, and NULL. One report calculates AVG(amount). Another calculates SUM(amount) / COUNT(*) using decimal division.

Which result and explanation are correct?

  1. AVG(amount) is 15.0, while the manual calculation is 7.5 because their denominators differ (correct answer)
  2. Both expressions return 15.0 because aggregate functions consistently ignore NULL rows
  3. AVG(amount) is 7.5, while the manual calculation is 15.0 because AVG counts NULL rows
  4. Both expressions return 7.5 because NULL amounts are automatically converted to zero
Explanation: When working with aggregate functions and NULLs in SQL, the critical rule to internalize is: aggregate functions like AVG, SUM, and COUNT(column) ignore NULLs, but COUNT(*) counts every row, including those with NULLs. With values 10.0, NULL, 20.0, NULL, AVG(amount) sums only the non-NULL values and divides by their count: 10.0+20.02=15.0\frac{10.0 + 20.0}{2} = 15.0. Meanwhile, SUM(amount) / COUNT(*) computes 30.04=7.5\frac{30.0}{4} = 7.5, because COUNT(*) counts all four rows regardless of NULLs. So A is correct — the two expressions produce different results (15.0 vs. 7.5) precisely because their denominators differ. B is the most tempting trap. Yes, aggregate functions ignore NULLs consistently, but "consistently" doesn't mean both expressions behave the same. COUNT(*) is the outlier — it never ignores NULLs, which breaks the symmetry B assumes. C gets the numbers backwards and the explanation wrong. AVG does not count NULL rows; it's the manual COUNT(*) approach that inflates the denominator to 4, yielding 7.5. D is a flat misconception. SQL never silently converts NULL to zero. That behavior doesn't exist unless you explicitly use COALESCE(amount, 0) or similar. Study tip: Always ask yourself whether a COUNT references a column (COUNT(col) → ignores NULLs) or uses a star (COUNT(*) → counts everything). That single distinction drives a large category of SQL NULL-handling exam questions.

Question 10

Customer C1 has no orders. Customer C2 has one order with status pending. Customer C3 has one order with status complete.

Which customers are returned by this query?

SELECT c.customer_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.status = 'complete';

  1. C1 and C3, because the outer join preserves C1
  2. C2 and C3, because both customers have orders
  3. C1, C2, and C3, because the join is a left join
  4. C3 only, because the filter rejects NULL and nonmatching statuses (correct answer)
Explanation: When you see a LEFT JOIN combined with a WHERE clause filter, you need to think carefully about the order of operations — the join happens first, then the filter is applied to the result. Here's what the LEFT JOIN produces before any filtering: C1 appears with NULL in all order columns (no matching order), C2 appears with its pending order, and C3 appears with its complete order. At this stage, the left join has done its job of preserving all three customers. But then the WHERE clause o.status = 'complete' runs against this intermediate result. For C1, o.status is NULL — and NULL compared to anything using = evaluates to false, so C1 is eliminated. For C2, o.status is 'pending', which doesn't match 'complete', so C2 is also eliminated. Only C3 survives. D is correct. A is wrong because it assumes the LEFT JOIN's row-preservation survives the WHERE clause — it doesn't. Filtering on a non-NULL condition in the WHERE clause effectively undoes the outer join's preservation of unmatched rows. B is wrong because having any order isn't enough; the filter requires the status to specifically be 'complete', which eliminates C2. C is the most tempting trap: yes, it's a LEFT JOIN, but the WHERE clause overrides the join's behavior for rows where the outer-join columns are NULL or non-matching. The key strategy to remember: a WHERE clause that filters on a column from the right-side table of a LEFT JOIN will silently convert it into an INNER JOIN. If you want to filter while preserving unmatched rows, move that condition into the ON clause instead.