NULL and three-valued logic

NULL represents an unknown or absent value, not a special number and not an empty string. In the fixture, role 3 has unknown salary, while role 8 has a known illustrative zero. Those observations must remain distinct when measuring coverage or filtering published values.

The worked query asks specifically for unknown salaries, so it uses IS NULL. It returns role 3. Replacing the predicate with salary equals zero would answer a different question and return role 8 instead, even though both values can look empty in a careless interface.

Ordinary comparisons with NULL generally produce unknown. WHERE keeps true rows, so salary = NULL does not find missing salaries. IS NULL and IS NOT NULL are the explicit tests. SQL boolean expressions therefore need true, false and unknown in their reasoning.

COALESCE selects the first non-null argument, but it encodes a business decision. It can be appropriate for a display fallback without being appropriate for an average. Keep the original field available when measuring how much data was actually observed.

The counterexample uses equality to NULL and returns no rows. A second common mistake replaces all unknown salaries with zero before aggregation, lowering the average while pretending the missing observations were known. Neither mistake is fixed by adding a different ORDER BY.

A useful test fixture includes at least one missing observation and one genuine zero. If both produce the same intermediate representation, the query or client has already lost information. The correct action depends on the question, so document the intended missing-value policy.

Find every role with a known salary and return its identity and salary, including the zero observation. Predict seven rows and specifically confirm that role 8 remains. Avoid a truthiness-style condition such as salary greater than zero.

Use IS NOT NULL as the population rule, then sort by identity. This task changes only the missingness test and output shape. Explain why a known zero is included without making a claim that the fictional value is a realistic market salary offer.

Step 1: Identify the missing and zero rows. Step 2: Use the opposite missingness test for known observations.

The solution excludes only role 3 and retains role 8 with zero. Its acceptance result checks identities as well as values, which prevents a filter from silently converting missingness into a pay threshold. No fabricated replacement value is introduced.

For production reporting, show both the statistic and its observed denominator. An average computed from seven known observations is different evidence from one computed from eight complete observations. Later aggregation lessons make these denominator differences explicit.

Expect interview questions about NULL comparisons, NOT IN and aggregates. Start with the question being asked, then trace unknown through the expression. Avoid saying NULL simply equals nothing; that phrase hides how conditions and joins actually behave.

This lesson establishes the missing-value distinction used throughout the remaining tracks. You will revisit it when counting outer joins, computing averages, filtering anti-joins and ordering tied values. Preserve unknown observations until the business contract authorizes a replacement.

Optional stretch — after the core task works: Compare AVG(salary) with AVG(COALESCE(salary,0)) and explain their different denominators.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.