NULL is about missing or unknown information — and SQL treats that differently.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
SQL does not use ordinary two-valued Boolean logic when NULL is involved. A comparison with an unknown value usually produces UNKNOWN, and WHERE keeps only TRUE.
If Salary is NULL, the predicate Salary > 50000 is not FALSE. SQL cannot know whether it is true or false, so the result is UNKNOWN.
-- Salary = NULL
Salary > 50000 --> UNKNOWN
Salary = 50000 --> UNKNOWN
Salary <> 50000 --> UNKNOWN
-- WHERE only keeps TRUE rows.Once UNKNOWN becomes part of your mental model, NULL behavior stops looking random.
NULL is not compared with = or <>. SQL provides dedicated predicates: IS NULL and IS NOT NULL.
A filter like ClosedDate = NULL returns no TRUE matches under normal semantics. ClosedDate IS NULL expresses the actual question.
-- Correct
WHERE ClosedDate IS NULL
-- Correct
WHERE ClosedDate IS NOT NULL
-- Incorrect logic
WHERE ClosedDate = NULL
WHERE ClosedDate <> NULLNULL tests should be explicit so both SQL Server and the next developer know exactly what you mean.
One of SQL's most famous NULL surprises appears in NOT IN. If the subquery includes NULL, the predicate can become UNKNOWN for rows you expected to keep.
If you ask for CustomerID NOT IN (1,2,NULL), SQL cannot prove that CustomerID is different from the unknown value, so the result may not be TRUE.
-- Risky if BlockedCustomerID contains NULL
WHERE CustomerID NOT IN
(
SELECT BlockedCustomerID
FROM dbo.BlockedCustomer
)
-- Safer
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.BlockedCustomer AS b
WHERE b.BlockedCustomerID = c.CustomerID
);If NULLs are possible, NOT EXISTS is usually the clearer and safer anti-join pattern.
Outer joins intentionally create NULLs for missing matches. A filter placed in WHERE can then remove those NULL-extended rows, changing the practical meaning of the join.
A LEFT JOIN to Orders keeps customers without orders. But WHERE o.Status = 'Open' removes rows where o.Status is NULL, so customers without orders disappear.
-- Preserves customers with no OPEN orders
SELECT c.CustomerID, o.OrderID
FROM dbo.Customer AS c
LEFT JOIN dbo.Orders AS o
ON o.CustomerID = c.CustomerID
AND o.Status = 'Open';
-- This removes customers with no order:
SELECT c.CustomerID, o.OrderID
FROM dbo.Customer AS c
LEFT JOIN dbo.Orders AS o
ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Open';With outer joins, predicate location is part of the result logic — not just formatting.
Most aggregates ignore NULL inputs. That is useful, but it can also hide missing data if you assume NULL equals zero.
An average salary over non-NULL salaries answers a different question than an average where missing salaries are treated as zero.
SELECT
COUNT(*) AS TotalRows,
COUNT(Salary) AS RowsWithSalary,
AVG(Salary) AS AvgKnownSalary,
AVG(COALESCE(Salary,0)) AS AvgTreatMissingAsZero
FROM dbo.Employee;Before using COALESCE in aggregates, decide whether missing really means zero in the business process.
COALESCE and ISNULL are useful for presenting fallback values, but substituting a value for NULL changes semantics. Missing and zero, missing and empty string, or missing and 'Unknown' are not always interchangeable.
Displaying NULL City as 'Unknown' may be fine for a report. Using COALESCE(City,'') in a join or uniqueness rule may create very different behavior.
-- Presentation fallback
SELECT COALESCE(City, 'Unknown') AS DisplayCity
FROM dbo.Customer;
-- Be careful using the same pattern in predicates:
WHERE COALESCE(City, '') = @City;Use COALESCE/ISNULL intentionally: they are transformations, not invisible NULL erasers.
NULL semantics affect schema design too. Optional attributes that must be unique when present require more thought than simply adding a generic UNIQUE constraint.
A system may allow missing ExternalID, but if ExternalID is present it must be unique. A filtered unique index can express that rule cleanly.
CREATE UNIQUE INDEX UX_Customer_ExternalID
ON dbo.Customer(ExternalID)
WHERE ExternalID IS NOT NULL;Schema-level NULL rules make data quality enforceable instead of merely conventional.
NULL problems are rarely syntax problems alone. They come from unclear business meaning: unknown, not applicable, not yet captured, intentionally absent or failed to load may all deserve different treatment.
If NULL means three different things in the same column, no clever predicate can fully recover the lost semantics.
1. What does NULL mean in this column?
2. Is NULL different from zero / empty / unknown label?
3. Are predicates using IS NULL / IS NOT NULL?
4. Could NOT IN receive NULLs?
5. Could a LEFT JOIN WHERE filter remove unmatched rows?
6. Are COUNT(*) and COUNT(col) being interpreted correctly?
7. Does COALESCE/ISNULL change business meaning?
8. Are optional-unique rules enforced correctly?
9. Have NULL edge cases been tested explicitly?
10. Are application parameter types / defaults consistent with DB nullability?NULL becomes manageable when missing data has explicit meaning and SQL logic reflects that meaning.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
Because missing information prevents some comparisons from being known as TRUE or FALSE.
Equality with NULL evaluates UNKNOWN; use dedicated NULL predicates.
NOT EXISTS avoids the classic NULL contamination problem of NOT IN.
COUNT(*) counts rows; COUNT(Column) counts non-NULL values.
Unmatched right-side values are NULL, so WHERE may filter those rows out.
| Situation | Safer pattern |
|---|---|
| Column = NULL | Column IS NULL |
| Column <> NULL | Column IS NOT NULL |
| NOT IN (nullable subquery) | NOT EXISTS |
| Optional right-side filter in LEFT JOIN | Consider placing it in ON to preserve unmatched left rows |
| COUNT(*) vs COUNT(col) | Remember: rows vs non-NULL values |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
NULL semantics are a data-modeling concern and a query-logic concern at the same time.