SQL - NULL
Basic notes
- NULL means the value is missing or unknown. For example, an employee’s salary has not been entered yet.
0means the value is zero.''means empty text. Neither means NULL.- Use
IS NULLto find missing values andIS NOT NULLto find values that are present. - A missing value is not automatically treated as zero in calculations.
NOT NULLon a column means you must provide a value.
The examples below use Microsoft SQL Server. Examples with Employees assume this small table:
| Name | Salary |
|---|---|
| Asha | 10000 |
| Ravi | 20000 |
| Neha | NULL |
Think of Neha’s salary as “we don’t know yet.” This helps explain most NULL rules. Microsoft: NULL basics.
10 interview questions
1. How do you find rows with NULL? Is NULL equal to NULL?
Use IS NULL, never = NULL.
SELECT Name
FROM Employees
WHERE Salary IS NULL;
-- NehaTo find employees whose salary is known:
SELECT Name
FROM Employees
WHERE Salary IS NOT NULL;
-- Asha and RaviEven NULL = NULL is not TRUE. Two unknown salaries might be equal, or they might be different. SQL calls that result UNKNOWN.
Remember: To show a label with CASE, write CASE WHEN Salary IS NULL THEN 'Missing' ELSE 'Present' END.
2. Why does “salary is not 10000” also exclude NULL?
SELECT Name
FROM Employees
WHERE Salary <> 10000;
-- Ravi onlySQL knows Ravi’s salary is different from 10000. It cannot make that decision for Neha because her salary is unknown.
WHERE keeps a row only when the condition is TRUE. Both FALSE and UNKNOWN are left out.
To include missing salaries, say so explicitly:
SELECT Name
FROM Employees
WHERE Salary <> 10000 OR Salary IS NULL;
-- Ravi and NehaThe same idea explains these interview rules:
| Condition | Result | Easy way to think about it |
|---|---|---|
| TRUE OR UNKNOWN | TRUE | One true condition is enough |
| FALSE AND UNKNOWN | FALSE | One false condition makes AND fail |
| TRUE AND UNKNOWN | UNKNOWN | We still need to know the other condition |
| FALSE OR UNKNOWN | UNKNOWN | The unknown condition decides the answer |
| NOT UNKNOWN | UNKNOWN | Reversing “I don’t know” still means “I don’t know” |
3. Why is NULL inside NOT IN a common trap?
You want salaries that are not in a list:
SELECT Name
FROM Employees
WHERE Salary NOT IN (10000, NULL);
-- No rows!For Ravi, SQL must check both:
- Is 20000 different from 10000? Yes.
- Is 20000 different from an unknown value? Unknown.
SQL cannot confirm that Ravi passes the whole condition, so it leaves him out too.
Fix: Remove NULLs from a subquery used with NOT IN:
-- BlockedSalaries is another table containing salaries to exclude.
SELECT Name
FROM Employees
WHERE Salary NOT IN (
SELECT Salary
FROM BlockedSalaries
WHERE Salary IS NOT NULL
);Another common solution is NOT EXISTS. It asks: “Is there no matching row?”
SELECT e.Name
FROM Employees AS e
WHERE NOT EXISTS (
SELECT 1
FROM BlockedSalaries AS b
WHERE b.Salary = e.Salary
);Remember: This NOT EXISTS includes Neha because her NULL salary matches nothing. Add e.Salary IS NOT NULL AND before NOT EXISTS if you want to exclude missing salaries. Microsoft: IN and NOT IN.
4. How do COUNT, SUM, and AVG handle NULL?
Using our three employees:
| Expression | Result | Why |
|---|---|---|
COUNT(*) | 3 | Counts every row |
COUNT(Salary) | 2 | Counts only known salaries |
SUM(Salary) | 30000 | Adds 10000 + 20000 |
AVG(Salary) | 15000 | Divides 30000 by 2, not 3 |
MIN(Salary) | 10000 | Smallest known salary |
MAX(Salary) | 20000 | Largest known salary |
SELECT COUNT(*) AS EmployeeCount,
COUNT(Salary) AS KnownSalaryCount,
AVG(Salary) AS AverageSalary
FROM Employees;
-- 3, 2, 15000Main trap: NULL is ignored by AVG; it is not counted as zero. Replacing Neha’s salary with zero would change the average to 10000.
If all salaries are NULL, COUNT(Salary) is 0, while SUM, AVG, MIN, and MAX return NULL. With no rows, a query like the one above still returns one result row: the counts are 0 and the average is NULL. A query with GROUP BY has no groups to return for empty input.
Extra count trap: COUNT(1) counts all rows, just like COUNT(*). Zero is also a value, so COUNT(CASE WHEN Salary > 10000 THEN 1 ELSE 0 END) counts everyone. Leave out ELSE 0 to count only matches. Microsoft: COUNT, AVG.
5. What is the difference between ISNULL and COALESCE?
Both can provide a replacement when a value is NULL. They do not update the stored data when used in SELECT.
ISNULL(value, replacement) checks one value:
SELECT Name, ISNULL(Salary, 0) AS DisplaySalary
FROM Employees;
-- Neha's DisplaySalary is 0COALESCE checks several values from left to right and returns the first one that is not NULL:
SELECT COALESCE(NULL, NULL, 'Available', 'Backup');
-- AvailableCommon SQL Server trap: ISNULL usually keeps the first input’s data type and size. A replacement can get cut short.
DECLARE @Name varchar(3) = NULL; -- Holds at most 3 characters
SELECT ISNULL(@Name, 'Unknown'); -- Unk
SELECT COALESCE(@Name, 'Unknown'); -- UnknownCOALESCE considers the types of all inputs. Avoid mixing unrelated types: COALESCE('abc', 0) fails because SQL Server tries to convert 'abc' to a number.
Remember: ISNULL takes two inputs. COALESCE takes two or more and is available in other SQL database systems too. Microsoft: ISNULL, COALESCE.
6. Why do NULLs appear after a LEFT JOIN?
A LEFT JOIN keeps every row from the left table. When there is no matching row on the right, SQL fills the right-side columns with NULL.
Suppose Asha has order 101 and Neha has no orders:
SELECT c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerId = o.CustomerId;| Name | OrderId |
|---|---|
| Asha | 101 |
| Neha | NULL |
Common trap: Adding WHERE o.Status = 'Paid' removes Neha. Her missing order status cannot equal 'Paid'.
To keep every customer and show only their paid orders, put that condition in ON:
SELECT c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerId = o.CustomerId
AND o.Status = 'Paid';Remember: To find customers with no orders, use the first query with WHERE o.OrderId IS NULL, assuming real orders always have an OrderId. Also, two NULL join keys do not match through =. Microsoft: joins and NULLs.
7. Do table rules allow NULL?
These rules are called constraints. Their NULL behavior is a popular SQL Server interview topic.
| Rule | What happens with NULL? |
|---|---|
NOT NULL | Missing values are rejected |
PRIMARY KEY | NULL is not allowed |
UNIQUE on one column | Only one NULL is allowed in SQL Server |
CHECK (Age >= 18) | NULL is allowed: SQL cannot prove the unknown age breaks the rule |
DEFAULT 18 | An explicitly supplied NULL stays NULL if the column allows it |
A single-column FOREIGN KEY that allows NULL | NULL is allowed without a matching parent row |
Two traps to remember:
- To require a known adult age, use both
NOT NULLandCHECK (Age >= 18). - A default is used when you omit the column or write
DEFAULT; it does not replace an explicit NULL.
To allow many missing emails but prevent duplicate known emails, use a filtered unique index. The filter means “apply uniqueness only to these rows”:
CREATE UNIQUE INDEX UX_Users_Email
ON Users(Email)
WHERE Email IS NOT NULL;For a UNIQUE rule covering multiple columns, SQL checks the whole combination: (1, NULL) and (2, NULL) can coexist; a second (1, NULL) cannot. Microsoft: UNIQUE and CHECK, defaults and foreign keys, filtered indexes.
8. How are NULLs grouped, counted as distinct, and sorted?
Suppose a column contains: 10, 10, 20, NULL, NULL.
| Operation | Result |
|---|---|
SELECT DISTINCT | Three values: 10, 20, NULL |
COUNT(DISTINCT column) | 2 — counts 10 and 20, ignores NULL |
GROUP BY column | Three groups: 10, 20, and one group for NULLs |
ORDER BY column ASC | NULL, NULL, 10, 10, 20 |
ORDER BY column DESC | 20, 10, 10, NULL, NULL |
UNION removes duplicate rows, including duplicate NULL rows. UNION ALL keeps them.
To sort salaries from low to high but put missing salaries last:
SELECT Name, Salary
FROM Employees
ORDER BY CASE WHEN Salary IS NULL THEN 1 ELSE 0 END,
Salary;
-- Asha, Ravi, NehaThe CASE gives known salaries 0 and missing salaries 1, so known salaries come first. SQL Server does not support NULLS LAST syntax.
Remember: Grouping NULLs together does not change the comparison rule: NULL = NULL is still UNKNOWN. Microsoft: GROUP BY, COUNT, ORDER BY, UNION.
9. What happens when you calculate or combine text with NULL?
Math with a missing value usually produces a missing result:
SELECT 100 + NULL;
-- NULL: 100 plus an unknown amount is still unknownFor text, + and CONCAT behave differently on modern SQL Server:
SELECT 'Hello' + CAST(NULL AS varchar(10));
-- NULL
SELECT CONCAT('Hello', NULL, '!');
-- Hello!CAST tells SQL the NULL in the first example is text. CONCAT treats NULL as empty text.
Related interview trick: avoid division by zero with NULLIF.
NULLIF(a, b) returns NULL when the two inputs are equal. Otherwise, it returns the first input.
SELECT NULLIF(0, 0); -- NULL
SELECT NULLIF(5, 0); -- 5
SELECT 100.0 / NULLIF(0, 0); -- NULL instead of a divide-by-zero errorRemember: An undefined result and zero mean different things. Only replace NULL with zero when that meaning is appropriate. Microsoft: CONCAT, NULLIF.
10. What if I want two NULLs to count as equal?
Sometimes you compare old and new data and want “both missing” to mean “no change.”
In SQL Server 2022 and later, use IS NOT DISTINCT FROM. Read it as “the same, including when both are NULL.”
| A | B | A IS NOT DISTINCT FROM B |
|---|---|---|
| 10 | 10 | TRUE |
| 10 | 20 | FALSE |
| NULL | 10 | FALSE |
| NULL | NULL | TRUE |
DECLARE @OldSalary int = NULL;
DECLARE @NewSalary int = NULL;
SELECT CASE
WHEN @OldSalary IS NOT DISTINCT FROM @NewSalary THEN 'Same'
ELSE 'Changed'
END;
-- SameIS DISTINCT FROM does the opposite: it finds differences, including one missing value compared with one known value.
On older SQL Server versions, explicitly check for both values being NULL:
SELECT CASE
WHEN @OldSalary = @NewSalary
OR (@OldSalary IS NULL AND @NewSalary IS NULL)
THEN 'Same'
ELSE 'Changed'
END;Remember: Avoid replacing NULL with a made-up value like -1 just to compare. If -1 ever appears in real data, the comparison can give the wrong answer. Microsoft: IS DISTINCT FROM.