Recommended Free Tools
NULL means a value is missing, unknown, or inapplicable—not zero and not an empty string. To test for it, use IS NULL, not = NULL. The distinction matters because SQL comparisons involving NULL can evaluate to UNKNOWN, which changes which rows a query returns.
How do you check for NULL in SQL?
Use IS NULL to find rows with a null value and IS NOT NULL to find rows with a known value. These predicates test whether a column is null; they do not compare its contents with an ordinary value.
As an Amazon Associate I earn from qualifying purchases.
-- Incorrect: this comparison does not evaluate TRUE for NULL
SELECT * FROM customers WHERE middle_name = NULL;
-- Correct: test whether the value is NULL
SELECT * FROM customers WHERE middle_name IS NULL;Microsoft’s SQL Server documentation on NULL and UNKNOWN summarizes the distinction: “A null value is different from an empty or zero value.” An empty string is a known value containing no characters; zero is a known numeric value. NULL says the value is not known or does not apply. Microsoft likewise recommends IS NULL or IS NOT NULL to test for nulls.
The same mental model is useful across SQL engines, although details of functions and edge cases can differ by database. In particular, NULL = NULL is not TRUE: SQL treats the result as unknown because there are no known values to compare.
#1 Best Overall
Why doesn’t = NULL work? Understand UNKNOWN
SQL uses three-valued logic: a condition can be TRUE, FALSE, or UNKNOWN. A comparison involving a null value generally produces UNKNOWN, rather than true or false. The PostgreSQL 16 documentation on logical operators shows how unknown results behave with AND, OR, and NOT.
A WHERE clause retains rows for which its condition is true. A row whose condition is false or unknown is not returned. For example, if status is null, the predicate status <> 'closed' is unknown, so that row is filtered out.
-- Excludes rows whose status is NULL
SELECT * FROM tickets
WHERE status <> 'closed';
-- Includes NULL statuses as well as statuses other than 'closed'
SELECT * FROM tickets
WHERE status <> 'closed' OR status IS NULL;Choose the second condition only if an unknown status should count as part of the result. Negation does not fix the issue: NOT (status = 'closed') remains unknown when status is null, rather than becoming true.
Free tools Windows power users keep installed
One-click scans. No signup required.
When should you use COALESCE or NULLIF?
These functions handle nulls in different ways. Choose based on what the data means, not just on how to make a query return a value.
| Need | Use | Effect |
|---|---|---|
| Test whether a value is missing | IS NULL or IS NOT NULL |
Tests the null state without replacing the value. |
| Show a fallback when a value is missing | COALESCE |
Returns the first non-null argument in the query result; does not update stored data. |
| Treat a chosen sentinel as missing | NULLIF |
Returns null if its two arguments compare equal; otherwise returns the first argument. |
Use COALESCE for an intentional fallback
COALESCE returns the first argument that is not null. For example, this PostgreSQL-supported expression displays a nickname when present, otherwise a full name, and otherwise a label:
SELECT COALESCE(nickname, full_name, '(unnamed)') AS display_name
FROM people;The expression changes the value shown by this query, not the value stored in the table. PostgreSQL requires its arguments to be convertible to a common type. See the PostgreSQL 14 documentation on conditional expressions for its documented behavior.
Do not replace null with 0 or '' automatically. Zero may represent a real quantity, and an empty string may be a valid known value. A fallback can change filtering, arithmetic, aggregates, and the meaning of reported results. Use one only when it accurately represents what a missing value should mean in that context.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsUse NULLIF only when a sentinel has a defined meaning
NULLIF(a, b) returns null when a and b compare equal; otherwise it returns a. If an application uses an empty discount code to mean “no code,” a query can normalize that convention:
SELECT NULLIF(discount_code, '') AS discount_code
FROM orders;This is appropriate only if the application has deliberately assigned that meaning to the empty string. A known empty string and an unknown value are distinct facts; NULLIF deliberately collapses them for this expression.
What does NULL do to counts, groups, and sort order?
Aggregate, grouping, and ordering behavior should be checked in the documentation for your database engine. For MySQL, the MySQL 26.7 manual documents these behaviors:
COUNT(column)counts non-null values in that column; aggregate functions such asMINandSUMgenerally ignore null inputs.COUNT(*)counts rows, including rows where a particular column is null.- Null values are treated as equal for
DISTINCTandGROUP BY, so nulls form a single group. - With MySQL’s
ORDER BY, nulls appear first by default and last when sorting in descending order.
For example, if five rows have been selected and only three have a known email, MySQL’s COUNT(*) reports five while COUNT(email) reports three. These are answers to different questions: how many rows are present, versus how many have a non-null email. Do not assume MySQL’s documented ordering or other edge behavior applies identically to another engine.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →SQL Server: COALESCE and ISNULL are not interchangeable
In Transact-SQL, ISNULL accepts two arguments, while COALESCE accepts a list. Microsoft also documents differences in result type, nullability metadata, and evaluation. SQL Server rewrites COALESCE in a CASE-like way, so input expressions can be evaluated more than once; a subquery argument, for example, can be evaluated twice. These differences can matter in computed columns, constraints, or expressions involving nondeterministic inputs.
Consult Microsoft’s SQL Server COALESCE documentation when choosing between the functions in a SQL Server expression. PostgreSQL documents short-circuit-style evaluation for arguments it does not need, while cautioning that this does not prevent every planning-time error. Function behavior should therefore be understood in the context of the specific engine.
Quick Recap
A quick checklist for NULL-safe queries
- Use
IS NULLandIS NOT NULLfor null tests—never= NULLor<> NULL. - For each filter, decide explicitly whether rows with missing values should be excluded or included.
- Use
COALESCEonly when the fallback has the intended meaning; useNULLIFonly when the matched value is truly a sentinel. - Check your database’s documentation for aggregate, ordering, grouping, and function-specific behavior.
- Test the query with representative rows containing a known value, an empty value where relevant, and
NULL.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
