A NULL in SQL does not mean zero, an empty string or “no”. It means the value is unknown or does not apply. A customer whose date of birth was never captured has a NULL birth date; that customer was certainly born, we just do not know when. Everything odd about how SQL handles NULL follows from taking that meaning seriously.
Three answers instead of two
Ordinary logic has two outcomes, true and false. SQL has three: true, false and unknown. Any comparison involving NULL gives unknown. Is an unknown discount greater than 10%? Unknown. Is an unknown city equal to “Chennai”? Unknown. Is an unknown value equal to another unknown value? Also unknown, because two missing birth dates are not known to be the same date.
The crucial rule is what filters do with unknown. A WHERE clause, a HAVING clause and a join condition keep a row only when the condition is true. False and unknown are both rejected. That is why a filter for city equal to NULL returns nothing, even when many rows have no city. The comparison is unknown for every row. To find missing values you must use the dedicated predicates IS NULL and IS NOT NULL, which always return true or false.
Unknown also propagates through AND, OR and NOT in a sensible way. True AND unknown is unknown, because the result depends on the missing piece. False AND unknown is false, because it cannot be true whatever the missing piece is. True OR unknown is true. NOT unknown is still unknown.
The filter that loses rows you wanted
Suppose a sales team wants all leads not from the “Partner” channel. The natural filter is channel not equal to “Partner”. Leads with a NULL channel give unknown for that comparison and are silently excluded. If 8% of leads have no channel recorded, the report undercounts by that much, and nobody notices because no error is raised.
The fix is to decide what missing should mean for this question and say it explicitly: either add “or channel is null” to the condition, or replace missing values with a label such as “Unknown” using COALESCE, which returns the first non-null value from its arguments.
Counting and averaging with gaps
Aggregate functions ignore NULL inputs, with one exception. Take a column of five discount amounts: ₹100, ₹200, missing, ₹400, missing.
| Expression | Result | What it counts |
|---|---|---|
COUNT(*) | 5 | Rows, regardless of content |
COUNT(discount) | 3 | Rows where discount is not null |
COUNT(DISTINCT discount) | 3 | Different non-null values |
SUM(discount) | 700 | Non-null values only |
AVG(discount) | 233.33 | 700 divided by 3, not by 5 |
COUNT(*) is the exception: it counts rows, so nulls are included. Every other aggregate skips them.
Whether the average of ₹233.33 is right depends on what the missing values mean. If missing means “this order had no discount”, the true average discount per order is ₹700 divided by 5, which is ₹140, and you should convert the nulls to zero first. If missing means “the discount was given but not recorded”, converting to zero would understate it, and ₹233.33 over the known values is the more honest figure, with a note about coverage. The database cannot make this call for you. It is a business decision about what the gap represents.
One more edge: SUM of a column where every value is NULL returns NULL, not zero. Dashboards often display that as a blank, which readers may misread as “no data loaded”. Wrapping the total in COALESCE with zero makes the intent clear when zero is genuinely correct.
The NOT IN trap
This one surprises experienced people. You want customers who are not on a blocked list, so you filter customer ID NOT IN a subquery returning blocked IDs. It works for months. Then someone inserts a row into the blocked table with a missing customer ID, and the query starts returning zero rows.
The reason: “x not in (1, 2, NULL)” expands to “x is not 1 AND x is not 2 AND x is not NULL”. The last part is unknown for every x. True AND unknown is unknown, so no row ever passes. NOT EXISTS does not have this problem, because it asks whether any matching row exists and a NULL simply never matches. Prefer NOT EXISTS for anti-joins, or make sure the subquery excludes nulls.
Other places NULL behaves differently
- Arithmetic and concatenation. Price plus a
NULLshipping charge isNULL. In most databases, joining a first name to aNULLlast name with the standard concatenation operator givesNULL. Oracle is a notable exception in that it treats an empty string asNULLand concatenation skips nulls. - Grouping. Although two nulls are not “equal” in a comparison,
GROUP BYplaces all nulls in a single group.DISTINCTandUNIONlikewise treat nulls as duplicates of each other. - Sorting. Where nulls appear in a sort varies. PostgreSQL and Oracle put them last in ascending order; MySQL and SQL Server put them first. PostgreSQL and Oracle support
NULLS FIRSTandNULLS LASTto make it explicit. - Joins. A null key matches nothing, so rows with missing keys vanish from inner joins.
- Division. To avoid a divide-by-zero error, analysts often wrap the denominator in
NULLIFwith zero, which turns a zero intoNULLso the ratio becomesNULLinstead of failing.
Handling NULL as a design decision
The temptation is to scrub every null into a zero or a placeholder at load time so that reports “just work”. That destroys information. A payment amount of zero and a payment amount nobody recorded are different facts, and a fraud analyst or an auditor will care about the difference.
A better habit is to keep nulls in the stored data, document what they mean column by column, and apply the right substitution at the point of analysis, where you know the question being asked. When you publish a metric built on a column with gaps, state the coverage: “average discount among the 62% of orders where discount was recorded” is less tidy than a single number, but it is true.
Written by the RP Strativa Team. Figures in the examples are illustrative.
Leave a Reply