NULL is the value that means "we do not know". It is not zero, it is not an empty string, and it does not equal anything, not even another NULL. Get comfortable with that one idea and a whole category of confusing SQL bugs simply disappears.
What this guide covers
1. What NULL actually means
A column is NULL when there is no value recorded for it. That is a statement about knowledge, not about content. A customer whose phone is NULL might have a phone that nobody entered, or might have no phone at all. Either way the database says: unknown. This is why NULL behaves differently from every ordinary value you work with.
The single most common beginner mistake is to treat NULL as if it were zero or an empty string. They are three different things, and the database keeps them distinct on purpose.
| Value | What it means | Takes storage? | Equals another of its kind? |
|---|---|---|---|
NULL | Unknown or missing | Marked absent | No (result is unknown) |
0 | The number zero, a real value | Yes | Yes |
'' | An empty string, a real value of length 0 | Yes | Yes |
Because NULL represents the unknown, arithmetic and string operations that touch it usually produce NULL. 5 + NULL is NULL, 'Mr ' || NULL is NULL, and NULL > 100 is not true or false but unknown. The idea of the unknown propagating through an expression is central, and the official SQL NULL reference spells out how the standard defines it. If you are still building the basics, the SQL fundamentals track is the place to start.
Quick rule: zero and empty string are answers. NULL is the absence of an answer. Never store NULL to mean "the number 0" or "the blank text".
2. Three valued logic (TRUE, FALSE, UNKNOWN)
Most programming languages use two valued logic: a condition is either true or false. SQL adds a third result, UNKNOWN, which appears whenever a comparison involves NULL. This is called three valued logic, and it drives almost every surprising NULL behaviour you will meet.
When you write WHERE price > 100 and price is NULL, the comparison returns UNKNOWN, not FALSE. A WHERE clause only keeps rows where the condition is exactly TRUE, so rows that evaluate to UNKNOWN are dropped just like FALSE rows. That looks the same in a simple filter, but it changes everything once NOT, AND and OR get involved.
| A | B | A AND B | A OR B | NOT A |
|---|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE | FALSE |
| TRUE | UNKNOWN | UNKNOWN | TRUE | FALSE |
| FALSE | UNKNOWN | FALSE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
Read the second row carefully. TRUE AND UNKNOWN is UNKNOWN, but TRUE OR UNKNOWN is TRUE, because with OR one true operand is enough regardless of the unknown. This is why a NULL slipping into a compound condition can quietly remove rows you expected to keep. To go deeper on how comparison and logical operators combine, see the SQL operators guide.
3. Testing for NULL correctly
Since NULL is never equal to anything, you cannot test for it with =. The expression x = NULL is always UNKNOWN, so the row is never returned, even when x really is NULL. This is the number one NULL trap, and it fails silently: no error, just an empty result.
-- returns zero rows, always
SELECT id FROM orders WHERE shipped_at = NULL;The correct test uses the dedicated operators IS NULL and IS NOT NULL. These are the only reliable way to ask whether a value is present.
-- unshipped orders
SELECT id FROM orders WHERE shipped_at IS NULL;
-- shipped orders
SELECT id FROM orders WHERE shipped_at IS NOT NULL;The same rule applies to inequality. WHERE status <> 'done' will not return rows where status is NULL, because NULL <> 'done' is UNKNOWN rather than TRUE. If you want those rows too, ask for them explicitly.
SELECT id FROM tasks
WHERE status <> 'done' OR status IS NULL;Some engines add helper predicates. Standard SQL offers IS DISTINCT FROM, which treats two NULLs as equal and a NULL against a value as different, giving a clean true or false with no UNKNOWN. PostgreSQL and modern SQL Server support it, and MySQL provides the equivalent null safe equality operator <=>.
4. NULL in WHERE, JOIN and GROUP BY
WHERE
As covered above, a WHERE clause keeps only rows that evaluate to TRUE. Rows evaluating to UNKNOWN are excluded, which is usually what you want but can hide edge cases when the filtered column contains NULLs.
JOIN
A join condition follows the same three valued logic. When you join a.dept_id = b.dept_id and one side is NULL, the comparison is UNKNOWN, so the rows do not match. This is exactly why LEFT JOIN exists: it keeps the left rows that found no match and fills the right side columns with NULL. Learn the join types in depth in the DQL commands reference.
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- employees with dept_id NULL still appear; d.dept_name is NULLGROUP BY
Here is the exception to the "NULL never equals NULL" rule. For grouping and for DISTINCT, SQL treats all NULLs as belonging to the same group. So GROUP BY country collapses every row with a NULL country into a single group, and that group holds all the rows where country is unknown.
SELECT country, COUNT(*) AS n
FROM customers
GROUP BY country;
-- one row where country IS NULL, holding every unknown-country customer5. NULL in DISTINCT, ORDER BY and aggregates
DISTINCT
Like grouping, DISTINCT considers all NULLs identical, so a distinct list of a nullable column contains at most one NULL entry, no matter how many NULL rows exist.
ORDER BY and NULLS FIRST / NULLS LAST
NULLs also sort, and where they land differs by engine. PostgreSQL and Oracle place NULLs last by default in ascending order, while MySQL and SQL Server place them first. The portable fix is the NULLS FIRST or NULLS LAST clause, supported by PostgreSQL, Oracle and SQLite.
-- push unknown prices to the bottom
SELECT name, price
FROM products
ORDER BY price ASC NULLS LAST;On MySQL, which lacks the clause, you emulate it by sorting on an IS NULL expression first: ORDER BY price IS NULL, price.
Aggregates skip NULL
Every aggregate function except COUNT(*) ignores NULL inputs. This matters more than it first appears, especially for averages. Consider a ratings column with values 4, 5 and NULL.
| Aggregate | Counts / uses NULL? | Result over {4, 5, NULL} |
|---|---|---|
COUNT(*) | Yes, counts every row | 3 |
COUNT(rating) | No, skips NULL | 2 |
SUM(rating) | No, skips NULL | 9 |
AVG(rating) | No, divides by 2 not 3 | 4.5 |
The trap is AVG. It divides the sum by the count of non-NULL values, so AVG(rating) is 9 / 2 = 4.5, not 9 / 3 = 3. If you truly want NULLs treated as zero in the average, convert them first with AVG(COALESCE(rating, 0)). Decide deliberately which behaviour you mean.
6. Handling functions: COALESCE, IFNULL, NULLIF
SQL gives you a small toolkit for turning NULLs into usable values, or turning unwanted values into NULLs. Learn these four and you will reach for them constantly.
COALESCE
COALESCE is the portable, standard function. It takes any number of arguments and returns the first one that is not NULL. Use it to supply a fallback for a missing value.
SELECT name,
COALESCE(nickname, first_name, 'Guest') AS display_name
FROM users;
-- picks nickname, else first_name, else the literal 'Guest'IFNULL, ISNULL and NVL
Each vendor also ships a two argument shortcut that returns the second value when the first is NULL. They do the same job but have different names, which is a common portability headache.
| Database | Two argument function | Example |
|---|---|---|
| MySQL | IFNULL(x, y) | IFNULL(phone, 'n/a') |
| SQL Server | ISNULL(x, y) | ISNULL(phone, 'n/a') |
| Oracle | NVL(x, y) | NVL(phone, 'n/a') |
| Any / standard | COALESCE(x, y) | COALESCE(phone, 'n/a') |
Because COALESCE works everywhere and accepts more than two arguments, prefer it unless you have a specific reason not to. The PostgreSQL conditional functions docs describe COALESCE and NULLIF precisely, and the MySQL guide to working with NULL covers IFNULL and the null safe operator.
NULLIF
NULLIF(a, b) is the mirror image: it returns NULL when a equals b, otherwise it returns a. The classic use is guarding against division by zero, by turning a zero denominator into NULL so the whole expression becomes NULL instead of raising an error.
-- no divide-by-zero error; ratio is NULL when total is 0
SELECT passed / NULLIF(total, 0) AS pass_ratio
FROM exam_stats;Another handy use is normalising a "junk" value to NULL, for example NULLIF(TRIM(note), '') converts blank notes into a proper NULL so downstream logic can rely on IS NULL.
7. The NOT NULL constraint and defaults
The cleanest way to avoid NULL headaches is to not allow NULLs where they make no sense. A NOT NULL constraint tells the database to reject any insert or update that leaves the column empty, guaranteeing every row has a real value.
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
deleted_at TIMESTAMP NULL -- NULL allowed on purpose
);Pair NOT NULL with a DEFAULT so inserts that omit the column still succeed with a sensible value. Notice that deleted_at is intentionally nullable: here NULL is meaningful and means "not deleted", which is a legitimate design. The skill is deciding, column by column, whether unknown is a state you need to represent. For the wider set of habits around this, see the SQL cheat sheet.
8. The classic NOT IN with NULL bug
This one deserves its own section because it is subtle, common, and produces wrong results without any error. When a subquery inside NOT IN returns even a single NULL, the entire NOT IN can evaluate to UNKNOWN for every row, so the query returns nothing.
The reason is pure three valued logic. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. That last comparison is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN, so no row can ever be TRUE.
-- returns 0 rows if any customer_id in orders is NULL
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);-- NOT EXISTS is null-safe
SELECT c.id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Prefer NOT EXISTS (or a LEFT JOIN ... WHERE o.customer_id IS NULL) whenever the subquery column might contain NULLs. If you must keep NOT IN, filter the NULLs out of the subquery with WHERE customer_id IS NOT NULL.
Watch out: NOT IN against a nullable column is a silent data bug. It does not error, it just returns an empty or wrong result set. Reach for NOT EXISTS by default. More of these traps live in common SQL mistakes.
Putting it together
NULL is not a quirk to memorise, it is a consistent idea: the database is honest about what it does not know, and it refuses to pretend the unknown behaves like a known value. Once you accept that, the rules follow naturally. Test with IS NULL, never =. Expect UNKNOWN in three valued logic. Remember aggregates skip NULLs and AVG shifts because of it. Reach for COALESCE to supply fallbacks and NULLIF to create NULLs on purpose. Use NOT NULL where absence is meaningless, and prefer NOT EXISTS over NOT IN. Master those and NULL stops being a source of bugs and becomes a precise tool. When you are ready to practise all of this hands on, the SQL beginner course walks through each case with runnable queries.