Question 1

The operator that actually finds missing data

Accepted Answer

IS NULL is the correct way to check for NULL values. The = operator always returns NULL (not true) when compared to NULL, so WHERE manager_id = NULL would return no rows. Only Alice has a NULL manager_id, so only her name is returned.

Question 2

Two unknowns compared

Accepted Answer

In SQL, NULL represents an unknown value. Comparing two unknowns produces another unknown, not true. This is why WHERE col = NULL never matches any rows - you must use IS NULL instead. This three-valued logic (true, false, null) is one of the most common sources of bugs in SQL.

Question 3

When two COUNTs on the same table disagree

Accepted Answer

COUNT(*) counts every row regardless of NULL values - there are 3 rows so it returns 3. COUNT(amount) counts only rows where amount is not NULL - row 2 has NULL so it returns 2. This difference is easy to miss and frequently causes off-by-one bugs in aggregation queries.

Question 4

Nickname or name as a fallback

Accepted Answer

COALESCE returns the first non-NULL argument. For Alice, nickname is 'Ace' (not NULL) so it returns 'Ace'. For Bob, nickname is NULL so it falls through to name, returning 'Bob'. COALESCE is the standard way to substitute a fallback value when a column might be NULL.

Question 5

Keep only products with a category

Accepted Answer

IS NOT NULL returns true only for rows where the column has an actual value. Gadget has a NULL category so it is excluded. Widget and Doohick both have 'Tools' as their category, so both are returned.

Question 6

Salary + NULL - what could go wrong?

Accepted Answer

Any arithmetic involving NULL produces NULL. salary + NULL is NULL, not salary. COALESCE(bonus, 0) converts NULL to 0 before the addition, so salary + 0 = salary. The last option, COALESCE(salary + bonus, 0), would return 0 for the whole expression if either operand is NULL - that is wrong if you want to preserve the salary.

Question 7

Why NULL isn't true, false, or either

Accepted Answer

SQL uses three-valued logic: true, false, and NULL (unknown). Any comparison with NULL produces NULL. The WHERE clause only includes rows where the condition is true - rows where the condition is NULL are excluded, just like false rows. This is why WHERE col = NULL never matches anything: the result is NULL, which is not true.

Question 8

A missing sale in the middle

Accepted Answer

SUM ignores NULL values, just like COUNT(column) does. It adds 100 + 200 = 300. The NULL row simply does not contribute. This behavior applies to AVG, MIN, MAX, and SUM - all aggregate functions skip NULLs. AVG(amount) would return 150, not 100, because it divides 300 by 2 (non-NULL rows), not 3.

Question 9

COALESCE with multiple fallbacks

Accepted Answer

COALESCE evaluates its arguments left to right and returns the first one that is not NULL. The first two arguments are NULL so they are skipped. The third argument is 42 - not NULL - so COALESCE returns 42 immediately without evaluating 99. You can chain as many fallbacks as you need; COALESCE is the standard tool for cascading defaults.

Question 10

What does NULL mean in SQL?

Accepted Answer

NULL means the value is absent or unknown - it is not equivalent to 0, '' (empty string), or false. This distinction matters because any arithmetic or comparison with NULL produces NULL, not an error. A common bug is writing WHERE column = NULL, which never matches any rows - you must write WHERE column IS NULL instead.

Question 11

IS NULL vs = NULL

Accepted Answer

In SQL, NULL represents an unknown value, so NULL = NULL is NULL (unknown), not TRUE. The WHERE clause only keeps rows where the condition evaluates to TRUE, so NULL conditions are treated like FALSE and filtered out. IS NULL is a special operator designed specifically to check for the absence of a value - it returns TRUE when the value is NULL.

Question 12

What does NOT NULL enforce?

Accepted Answer

NOT NULL is a column-level constraint enforced at write time - INSERT and UPDATE statements that would leave the column NULL are rejected. It does not imply uniqueness; you can have many rows with the same non-NULL value. A common design choice is to pair NOT NULL with a DEFAULT so that rows that omit the column get a sensible fallback instead of an error.

Question 13

Does AVG count the missing row?

Accepted Answer

AVG ignores NULL values entirely - it divides the sum of non-NULL values by the count of non-NULL values. Here it computes (1 + 2) / 2 = 1.5, not (1 + 2 + 0) / 3 = 1.0. This behavior is consistent across all aggregate functions: SUM, COUNT(col), MIN, and MAX all skip NULLs. If you want NULL to count as zero, use AVG(COALESCE(score, 0)) instead.

Question 14

The WHERE clause trap every beginner hits

Accepted Answer

dept_id = NULL produces UNKNOWN for every row, including rows where dept_id is itself NULL. UNKNOWN is never TRUE, so the WHERE clause never passes any row. To find rows with no department, write `WHERE dept_id IS NULL`. This is the single most common NULL mistake in SQL - always reach for IS NULL, never = NULL.

Question 15

Where does the unknown end up in the lineup?

Accepted Answer

In PostgreSQL, NULLs sort as greater than any non-NULL value, so they appear last in ASC order and first in DESC order. You can override this with NULLS FIRST or NULLS LAST: ORDER BY col ASC NULLS FIRST. Other databases differ - MySQL sorts NULLs first in ASC, so always check your database's behavior and use NULLS FIRST/LAST explicitly when it matters.

Question 16

Turning a sentinel value into nothing

Accepted Answer

NULLIF(a, b) returns NULL when a equals b, otherwise it returns a. Here NULLIF(value, 0) converts zeros to NULL. This is useful before division - NULLIF(denominator, 0) prevents division-by-zero errors because dividing by NULL produces NULL rather than an error.

Question 17

The subquery result that silences everything

Accepted Answer

This is one of SQL's most dangerous NULL traps. NOT IN with a NULL in the list returns no rows at all. The reason: NOT IN is equivalent to id != 1 AND id != 2 AND id != NULL. The last condition id != NULL evaluates to NULL (unknown), and unknown AND anything = unknown, so the WHERE clause never matches. Always filter NULLs out of subquery results used with NOT IN, or use NOT EXISTS instead.

Question 18

Aggregate over all NULLs

Accepted Answer

When every value is NULL, SUM returns NULL (not 0) because there is nothing to sum. COUNT(value) counts non-NULL values - there are none, so it returns 0. COUNT(*) counts rows regardless of content - there are 2 rows, so it returns 2. This distinction matters when you need to detect 'no data' vs 'data that sums to zero'.

Question 19

Two NULL keys walk into a JOIN...

Accepted Answer

JOIN conditions follow the same three-valued logic as WHERE. NULL = NULL is NULL, not true, so rows with NULL join keys never match. This is why INNER JOINs silently exclude rows with NULL foreign keys. If you need to match NULLs explicitly, you can use IS NOT DISTINCT FROM instead of =, which treats two NULLs as equal.

Question 20

Which rows slip through when active is unknown?

Accepted Answer

The WHERE clause passes rows where the full condition is true. For id=1: TRUE = TRUE is true, so the row is included. For id=2: FALSE = TRUE is false, and FALSE IS NULL is false - the row is excluded. For id=3: NULL = TRUE is NULL (not true), but NULL IS NULL is true - the OR short-circuits to true, so the row is included. This pattern is useful when NULL means 'treat as active'.

Question 21

What does negating an unknown give you?

Accepted Answer

score > 90 evaluates to UNKNOWN when score is NULL, and NOT UNKNOWN is also UNKNOWN - negating an unknown value doesn't make it known. This surprises most people who expect NOT to flip NULL into TRUE or FALSE. The WHERE clause only passes rows where the condition is TRUE, so this row is silently excluded.

Question 22

Which operator treats two NULLs as matching?

Accepted Answer

IS NOT DISTINCT FROM is a NULL-safe equality operator that uses two-valued logic (only TRUE or FALSE, never UNKNOWN). It returns TRUE when both sides are equal OR both sides are NULL. This is the correct tool when you need a JOIN or WHERE that treats two NULLs as matching. The standard `=` operator returns UNKNOWN for NULL = NULL, so those rows would be excluded.

Question 23

How many groups do two NULLs make?

Accepted Answer

GROUP BY treats all NULL values as equal and places them in a single group. The two NULL rows form one group with a count of 2, just like the two 'East' rows form their own group. This is a deliberate exception to normal NULL comparison rules - normally NULL = NULL is UNKNOWN, but GROUP BY collapses NULLs together so aggregation can work on them.

Question 24

NULL sort order across databases

Accepted Answer

T-SQL (SQL Server) treats NULL as less than any non-NULL value, so NULLs sort first in ASC order. PostgreSQL treats NULL as greater than any non-NULL value, so NULLs sort last in ASC. MySQL also sorts NULLs first in ASC. Because the behavior is engine-specific, always write NULLS FIRST or NULLS LAST explicitly when null sort position matters to your application.

Question 25

HAVING and UNKNOWN

Accepted Answer

HAVING follows the same 'accept TRUE' rule as WHERE. Bob's SUM(amount) is NULL because both of his rows are NULL, and NULL > 100 is UNKNOWN - not TRUE. So Bob's group is excluded. Alice's total is 300, which is greater than 100 (TRUE), so her group is included. The NULL-exclusion rule applies to HAVING filters exactly the same way it applies to WHERE.

Question 26

NULL-aware inequality: what gets filtered out?

Accepted Answer

IS DISTINCT FROM returns TRUE when two values are different, with full NULL awareness. Two NULLs are NOT distinct from each other (same as IS NOT DISTINCT FROM in reverse), so the NULL | NULL row is excluded. A NULL and a non-NULL are always distinct, so NULL | 5 is included. Compare this to `x <> y` which would return UNKNOWN for any row involving NULL.

Question 27

When does a CHECK constraint let NULL through?

Accepted Answer

CHECK constraints use the opposite rule from WHERE. WHERE accepts only TRUE; CHECK rejects only FALSE. When age is NULL, NULL >= 18 evaluates to UNKNOWN - and UNKNOWN is not FALSE, so the constraint does not reject the row. This asymmetry is intentional: a CHECK constraint is meant to catch values that are definitely wrong, not values that are uncertain.

Question 28

The hidden cost of a NULL in your exclusion list

Accepted Answer

NOT IN expands to `id != 1 AND id != 2 AND id != NULL`. The last condition evaluates to UNKNOWN for any id value, because no value can be proven unequal to an unknown. UNKNOWN ANDed with anything stays UNKNOWN, which the WHERE clause rejects. So even 5 and 10 are excluded - neither passes the filter. Always strip NULLs from a NOT IN list, or rewrite as NOT EXISTS.

Question 29

Does a NULL row survive NOT IN?

Accepted Answer

For the NULL row, `NULL NOT IN (1, 2, 3)` expands to `NULL != 1 AND NULL != 2 AND NULL != 3`. Every comparison with NULL is UNKNOWN, so the whole expression is UNKNOWN - that row is excluded. For v = 7, all three comparisons are TRUE, so 7 passes. The NULL row quietly disappears even though the list contains no NULL.

Question 30

IN vs NOT IN symmetry with NULLs

Accepted Answer

IN expands to `id = 1 OR id = 2 OR id = NULL`. The last comparison is UNKNOWN; FALSE OR FALSE OR UNKNOWN = UNKNOWN, so id = 5 is excluded from IN. NOT IN expands to `id != 1 AND id != 2 AND id != NULL`; the last comparison is UNKNOWN, and TRUE AND TRUE AND UNKNOWN = UNKNOWN, so id = 5 is also excluded from NOT IN. A NULL in the list makes both IN and NOT IN unreliable - neither can produce a definitive result for values outside the list.