NULL in Comparisons & Aggregates

Now that you know NULL represents “unknown” and that comparisons with it evaluate to UNKNOWN rather than TRUE or FALSE, let’s look at what that actually means for filtering, aggregate functions, and sorting — the places it trips people up most often in practice.

NULL in WHERE clauses

A WHERE clause keeps a row only when its condition evaluates to TRUE. Rows where the condition evaluates to UNKNOWN — which is what happens whenever NULL is involved in a direct comparison — are filtered out, exactly as if the condition were FALSE:

NULL rows silently excluded

SQL

-- If discount_pct is NULL for some rows, this WHERE clause
-- excludes them, even though "not less than 10" feels like it
-- should include "we don't know the discount"
SELECT product_name, discount_pct
FROM products
WHERE discount_pct >= 10;

-- To also see rows with a NULL discount, you must ask for it explicitly
SELECT product_name, discount_pct
FROM products
WHERE discount_pct >= 10 OR discount_pct IS NULL;

Aggregate functions ignore NULLs

Every standard aggregate function — SUM, AVG, MIN, MAX, and COUNT(column) — simply skips NULL values as if they weren’t there, rather than treating them as zero:

Aggregates skip NULL

SQL

-- Table 'scores': values 90, 80, NULL, 70
SELECT
  SUM(score)   AS total,   -- 240, not an error and not treating NULL as 0
  AVG(score)   AS average, -- 80  (240 / 3 rows with a value, NOT / 4)
  COUNT(score) AS scored,  -- 3   (counts only non-NULL values)
  COUNT(*)     AS all_rows -- 4   (counts every row, NULL or not)
FROM scores;

COUNT(column) vs COUNT(*)

These two are easy to confuse and produce different answers whenever NULLs are present:

Expression	What it counts
COUNT(*)	Every row in the result set, regardless of NULLs in any column
COUNT(column_name)	Only rows where that specific column is NOT NULL
COUNT(DISTINCT column_name)	Only distinct, non-NULL values in that column

This matters a lot in practice: COUNT(email) tells you how many customers have an email on file, while COUNT(*) tells you the total number of customers regardless of whether they have one.

NULL in ORDER BY

Where NULLs land when you sort a column depends on your database:

Sorting a nullable column

SQL

SELECT name, discount_pct
FROM products
ORDER BY discount_pct;

Dialect differences in NULL sort position

PostgreSQL and Oracle sort NULLs as the largest possible value by default, so they appear last in ascending order and first in descending order. MySQL and SQL Server sort NULLs as the smallest possible value, so they appear first in ascending order. All major databases let you override this explicitly with ORDER BY column NULLS FIRST or NULLS LAST (PostgreSQL, Oracle) or an equivalent CASE expression trick on databases that don’t support that syntax directly.

A row with a NULL in a filtered column is excluded unless you explicitly test for IS NULL
SUM, AVG, MIN, MAX, and COUNT(column) all ignore NULL values rather than treating them as zero
COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column — these can legitimately differ
NULL sort position in ORDER BY varies by database — check yours, or specify NULLS FIRST / NULLS LAST explicitly where supported