NULL Handling

What Is NULL?

NULL means the absence of a value — not zero, not an empty string, not a space. It means the value is unknown, missing, or not applicable. A product with no rating does not have a rating of 0 — it has no rating at all. A customer with no phone number does not have an empty string — the phone number is simply absent. This distinction matters enormously in SQL because NULL behaves differently from every other value in comparisons, arithmetic, and aggregation.

NULL is not a value you can compare with = or !=. Any comparison involving NULL — NULL = NULL, NULL = 0, NULL != 5 — returns neither true nor false but a third state: UNKNOWN. SQL Server's WHERE clause only keeps rows where the condition is true. Rows where the condition is UNKNOWN are silently excluded — exactly the same as rows where it is false. This catches developers off guard constantly: WHERE Rating = NULL will never return any rows, not even rows that genuinely have no rating.

• NULL means unknown or absent — not zero, not empty string, not false
• NULL = NULL evaluates to UNKNOWN, not TRUE — two unknowns are not considered equal
• Any arithmetic with NULL returns NULL — 100 + NULL = NULL, NULL * 5 = NULL
• The only correct way to test for NULL is IS NULL and IS NOT NULL
• Aggregate functions like SUM(), AVG(), MIN(), MAX() silently skip NULL values — COUNT(*) does not skip them, but COUNT(column) does

-- NULL is not a value -- it is the absence of one.
-- The ONLY correct operators for testing NULL are IS NULL and IS NOT NULL.
-- Using = NULL or != NULL always returns zero rows.
-- WRONG: this returns zero rows even if Rating is NULL on many products
SELECT Name, Rating
FROM Products
WHERE Rating = NULL;        -- = NULL always evaluates to UNKNOWN, never TRUE
-- CORRECT: IS NULL is the only operator that detects missing values
SELECT Name, Rating
FROM Products
WHERE Rating IS NULL;       -- returns rows where Rating has no value
-- CORRECT: IS NOT NULL returns rows where Rating does have a value
SELECT Name, Rating
FROM Products
WHERE Rating IS NOT NULL    -- only rows with an actual rating
ORDER BY Rating DESC;

• WHERE Rating = NULL returns zero rows every time — this is one of the most common SQL mistakes
• IS NULL and IS NOT NULL are the only operators that correctly detect missing values
• Two products (Standing Desk, USB-C Hub) have no rating — IS NULL finds them, = NULL finds nothing
• The IS NOT NULL result excludes those two products and returns only the five with actual ratings

NULL in Arithmetic and Concatenation

Any arithmetic operation that involves NULL produces NULL. Price * NULL, Stock + NULL, 100 / NULL — every one returns NULL, not an error and not zero. The same applies to string concatenation: concatenating a NULL with any string in T-SQL returns NULL by default. This means a calculated column that includes a NULLable field will silently produce NULL for every row where that field is missing — which can make aggregate results appear lower than expected or disappear entirely from reports.

-- NULL in arithmetic always produces NULL -- not zero, not an error.
-- Use ISNULL() or COALESCE() to substitute a default value.
-- ISNULL(expression, replacement) -- returns replacement if expression is NULL
-- COALESCE(val1, val2, val3...) -- returns the first non-NULL value in the list
SELECT
    Name,
    Rating,
    Stock,
    Price,
    -- NULL + anything = NULL -- discount calculation breaks silently
    Price * Rating                               AS RawScore,    -- NULL if Rating is NULL
    -- ISNULL substitutes 0 for missing ratings before calculating
    Price * ISNULL(Rating, 0)                    AS SafeScore,   -- 0 when no rating
    -- COALESCE picks the first non-NULL from a list
    COALESCE(CAST(Rating AS nvarchar), 'No rating') AS DisplayRating  -- text fallback
FROM Products
ORDER BY Rating DESC;

• Price * Rating returns NULL for Standing Desk and USB-C Hub — the NULL Rating poisons the entire calculation
• ISNULL(Rating, 0) substitutes 0 before the multiplication — the math completes and returns 0 instead of NULL
• COALESCE(CAST(Rating AS nvarchar), 'No rating') provides a readable fallback for display — the first non-NULL wins
• ORDER BY Rating DESC places NULL values last by default in SQL Server — NULL is treated as the lowest possible sort value

NULL in Aggregates — The Silent Skip

All aggregate functions except COUNT(*) silently skip NULL values. SUM(Rating) adds only the rows that have a rating. AVG(Rating) divides by the count of non-NULL rows, not the total row count — so the average can appear higher than expected. COUNT(*) counts every row including those with NULL in any column. COUNT(Rating) counts only rows where Rating is not NULL. Understanding this difference prevents silent calculation errors in reports.

-- Aggregate behaviour with NULL -- the silent skip can distort results.
-- COUNT(*) vs COUNT(column) is the most important distinction.
SELECT
    COUNT(*)                     AS TotalRows,       -- all 7 rows -- ignores NULLs in columns
    COUNT(Rating)                AS RowsWithRating,  -- only 5 -- skips the 2 NULL ratings
    COUNT(*) - COUNT(Rating)     AS MissingRatings,  -- 2 -- rows where Rating is NULL
    SUM(Rating)                  AS SumRating,       -- adds only the 5 non-NULL values
    AVG(Rating)                  AS AvgRating,       -- divides by 5 not 7 -- skips NULLs
    -- Manual AVG treating NULL as 0 -- divides by all 7 rows
    SUM(ISNULL(Rating, 0)) / COUNT(*) AS AvgWithZero,  -- lower because NULLs become 0
    MIN(Rating)                  AS MinRating,       -- lowest non-NULL rating
    MAX(Rating)                  AS MaxRating        -- highest non-NULL rating
FROM Products;

• COUNT(*) returns 7 — all rows — while COUNT(Rating) returns 5 — only rows where Rating is not NULL
• COUNT(*) - COUNT(Rating) is a clean pattern for counting how many rows have a missing value in any column
• AVG(Rating) divides the sum (22.30) by 5, giving 4.46 — the two NULL rows are excluded from both numerator and denominator
• The manual average treating NULL as 0 gives 3.19 — a very different result — choose which behaviour is correct for your use case

ISNULL, COALESCE, and NULLIF

SQL Server provides three functions for working with NULL. ISNULL(expression, replacement) is the T-SQL shorthand — it takes exactly two arguments and returns the replacement if the expression is NULL. COALESCE(val1, val2, ...) is the ANSI-standard equivalent but accepts any number of arguments, returning the first non-NULL in the list — useful when you have multiple fallback columns. NULLIF(a, b) does the reverse — it returns NULL if two values are equal, and the first value otherwise. NULLIF is most commonly used to prevent division-by-zero errors.

-- ISNULL, COALESCE, and NULLIF -- three tools for NULL control.
-- ISNULL: replace a NULL with a default value
SELECT
    Name,
    ISNULL(Brand, 'Unbranded')        AS Brand,      -- 'Unbranded' when Brand is NULL
    ISNULL(Rating, 0.0)               AS Rating      -- 0.0 when Rating is NULL
FROM Products;
-- COALESCE: first non-NULL from a priority list
-- Here: prefer PhoneNumber, fall back to Email, fall back to 'No contact'
SELECT
    FirstName,
    LastName,
    COALESCE(PhoneNumber, Email, 'No contact info')  AS ContactMethod
FROM Users;
-- NULLIF: returns NULL if the two values are equal -- prevents divide-by-zero
-- Without NULLIF, dividing by 0 causes a runtime error in SQL Server
SELECT
    Category,
    SUM(Total)                                 AS Revenue,
    COUNT(*)                                   AS Orders,
    SUM(Total) / NULLIF(COUNT(*), 0)           AS AvgOrderValue  -- safe division
FROM Orders o
JOIN OrderItems oi ON o.OrderId = oi.OrderId
GROUP BY Category;

• ISNULL(Rating, 0.0) replaces the two NULL ratings with 0.0 — the column now has no NULLs in the output
• COALESCE(PhoneNumber, Email, 'No contact info') tries each argument left to right and returns the first non-NULL — essential when contact data is incomplete
• NULLIF(COUNT(*), 0) returns NULL when the count is zero — dividing by NULL returns NULL instead of crashing with a divide-by-zero error
• ISNULL is slightly faster and simpler for two-argument cases; COALESCE is preferred when you need more than one fallback or want ANSI-compatible code

NULL in JOIN Conditions

NULL values in JOIN columns behave the same way as everywhere else — they never match anything, including other NULLs. If a foreign key column contains NULL, that row will not match any row in the joined table. It will disappear from an INNER JOIN result silently. A LEFT JOIN will preserve the row but show NULL for every column from the right table. This is often the intended behaviour, but it must be understood rather than stumbled into.

-- NULL in a JOIN column causes the row to match nothing.
-- INNER JOIN silently drops it. LEFT JOIN preserves it with NULLs on the right.
-- Find all products and their category description.
-- Some products may have a NULL CategoryId (not yet categorised).
-- INNER JOIN: products with NULL CategoryId are silently dropped
SELECT
    p.Name,
    p.Price,
    c.Description          AS CategoryDescription
FROM Products       p
JOIN Categories     c ON p.CategoryId = c.CategoryId;  -- NULL = anything → no match → row gone
-- LEFT JOIN: products with NULL CategoryId are kept -- right side shows NULL
SELECT
    p.Name,
    p.Price,
    ISNULL(c.Description, 'Uncategorised') AS CategoryDescription
FROM Products       p
LEFT JOIN Categories c ON p.CategoryId = c.CategoryId;  -- unmatched rows kept

• INNER JOIN silently drops Standing Desk and USB-C Hub because their NULL CategoryId matches nothing in Categories
• LEFT JOIN preserves them — the CategoryDescription columns from Categories come back as NULL, then ISNULL converts them to 'Uncategorised'
• This is why unexpectedly low row counts from joins often point to NULL values in join columns — the rows are not missing from the database, they simply failed to match
• Always check for NULLs in join columns when a query returns fewer rows than expected

NULL in WHERE with AND and OR

NULL's three-valued logic (TRUE / FALSE / UNKNOWN) produces surprising results when combined with AND and OR. TRUE AND UNKNOWN = UNKNOWN. FALSE OR UNKNOWN = UNKNOWN. But TRUE OR UNKNOWN = TRUE and FALSE AND UNKNOWN = FALSE. These rules mean that a condition involving a NULL column can silently exclude rows you expected to include — or include rows you expected to exclude — depending on how the conditions are combined.

-- NULL three-valued logic in WHERE conditions.
-- UNKNOWN is treated like FALSE -- the row is excluded.
-- Find products where Rating is below 4.5 OR Rating is NULL (no rating yet)
-- Without the IS NULL check, unrated products are silently excluded
-- MISSING the IS NULL check -- unrated products are silently dropped
SELECT Name, Rating
FROM Products
WHERE Rating < 4.5;            -- Rating IS NULL evaluates to UNKNOWN -- row excluded
-- CORRECT: explicitly include NULL rows with OR IS NULL
SELECT Name, Rating
FROM Products
WHERE Rating < 4.5
   OR Rating IS NULL;          -- now unrated products are included too
-- NOT IN and NULL -- a subtle trap
-- If the subquery can return NULL, NOT IN returns zero rows
-- This is one of the most dangerous NULL traps in SQL
-- SAFE: use NOT EXISTS instead of NOT IN when NULLs may be present
SELECT Name
FROM Products p
WHERE NOT EXISTS (
    SELECT 1
    FROM OrderItems oi
    WHERE oi.ProductId = p.ProductId  -- products that have never been ordered
);

• Without OR Rating IS NULL, the two unrated products are silently excluded — no error, just missing data
• Adding OR Rating IS NULL explicitly captures the rows where rating is absent
• NOT IN (subquery) returns zero rows if the subquery result contains even one NULL — this is a well-known trap; NOT EXISTS is the safe alternative
• Any time a WHERE condition on a NULLable column does not explicitly address NULL, missing values will be silently dropped from results

Lesson Summary

Practice Questions

Quiz

Next up — Table Relationships - How primary keys, foreign keys, and cardinality define the connections between tables and enforce data integrity across a database.