SQL NULL handling interview questions, with answers
NULL is the single most reliable way to lose marks in an SQL round, because every rule you learned about values quietly stops applying. NULL is not zero, not an empty string and not equal to itself, and half of the classic trick questions — the empty NOT IN, the average that ignores rows, the count that comes up short — are just that fact wearing a different hat.
Below are the NULL questions that come up in placement interviews, with the reasoning behind each answer. Then take the free SQL diagnostic: NULL handling is one of its twelve topics, and it will tell you whether the rule has actually stuck.
The questions, with answers
1.What does NULL mean in SQL, and why isn't it a value?
NULL is a marker for a missing or unknown value — a phone number the customer never gave, a manager the CEO does not have. It is not a value of the column's type: not 0, not an empty string, not false. Because it stands for "unknown", any comparison with it is also unknown, and SQL uses three-valued logic — TRUE, FALSE and UNKNOWN — to carry that through. WHERE keeps only rows whose condition is TRUE, so UNKNOWN behaves like FALSE there; that one sentence explains most of the questions below.
2.Why does WHERE column = NULL return no rows, and what should you write instead?
Because = NULL yields UNKNOWN for every row, including rows where the column really is NULL — "is this unknown thing equal to that unknown thing?" has no answer. UNKNOWN is not TRUE, so WHERE discards every row. The test for missing values is IS NULL, and its opposite is IS NOT NULL; both return TRUE or FALSE, never UNKNOWN. The same trap applies to <> NULL, and to comparing two nullable columns with each other. PostgreSQL offers IS NOT DISTINCT FROM and MySQL the <=> operator when you genuinely want NULL to compare equal to NULL.
SELECT * FROM Employees WHERE manager_id IS NULL; -- the CEO SELECT * FROM Employees WHERE manager_id = NULL; -- never any rows
3.How do aggregate functions treat NULL?
Every aggregate except COUNT(*) skips NULLs. SUM adds the non-NULL values, AVG divides the sum by the count of non-NULL values — not by the number of rows — and MIN and MAX ignore NULL entirely. With amounts 10, NULL and 30, AVG returns 20, not 13.33. If you want the NULLs treated as zero, say so with COALESCE(amount, 0) inside the aggregate. One more edge: an aggregate over zero non-NULL values returns NULL (SUM of nothing is NULL, not 0), except COUNT, which returns 0.
4.What is the difference between COALESCE, IFNULL, ISNULL and NVL?
They all substitute a fallback for NULL, but only COALESCE is standard SQL: it takes any number of arguments and returns the first non-NULL one, and it works in every major database. IFNULL(x, y) is MySQL and SQLite, two arguments. NVL(x, y) is Oracle. ISNULL(x, y) is SQL Server — and the trap is that in MySQL ISNULL(x) is a completely different function that returns 1 or 0 for whether x is NULL. In an interview, answer with COALESCE and mention the dialect-specific names; it shows you know which one is portable.
SELECT name, COALESCE(phone, mobile, 'not provided') AS contact FROM Customers;
5.What happens when NULL is used in arithmetic or string concatenation?
It propagates. 5 + NULL is NULL, salary * NULL is NULL, and in standard SQL 'abc' || NULL is NULL — so a total-pay column computed as salary + commission is NULL for anyone whose commission is NULL, which is the classic bug in a payroll query. Guard the nullable operand with COALESCE(commission, 0). Concatenation is where dialects diverge: Oracle treats NULL as an empty string in ||, MySQL's CONCAT returns NULL if any argument is NULL while CONCAT_WS skips NULLs, and PostgreSQL's || returns NULL but its CONCAT function ignores NULLs. Know your database before you promise a result.
6.Why does NOT IN return no rows when the subquery contains a NULL?
Because x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last comparison is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN, so the whole condition is never TRUE for any row — the query silently returns nothing. It is the most common NULL bug in interviews and in production. The fixes: add WHERE col IS NOT NULL inside the subquery, or rewrite as NOT EXISTS, which tests row existence and is immune to the problem. Positive IN is safe: x IN (1, NULL) is TRUE when x is 1 and UNKNOWN otherwise, which WHERE treats as false.
-- safe SELECT c.name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id);
7.Where do NULLs sort in ORDER BY?
It depends on the database, which is exactly why it is asked. PostgreSQL and Oracle treat NULL as larger than any value: NULLs come last in ascending order and first in descending. MySQL, SQL Server and SQLite treat NULL as smaller: first in ascending, last in descending. PostgreSQL, Oracle and SQLite (3.30 and later) let you say it explicitly with NULLS FIRST or NULLS LAST; in MySQL the idiom is ORDER BY col IS NULL, col, and in SQL Server a CASE expression as the first sort key does the same job. If a report must be stable across databases, always state the NULL position rather than relying on the default.
8.Does a UNIQUE constraint allow more than one NULL?
In most databases, yes. Because NULL is not equal to NULL, PostgreSQL, MySQL, Oracle and SQLite let a UNIQUE column hold any number of NULLs — the constraint only prevents two equal non-NULL values. SQL Server is the exception: it treats NULLs as equal for uniqueness, so a UNIQUE column there admits a single NULL, and you need a filtered unique index (WHERE col IS NOT NULL) to get the standard behaviour. A primary key never allows NULL at all, which is the cleanest way to explain the difference between PRIMARY KEY and UNIQUE.
How the diagnostic asks it
One question from the SQL bank, exactly as a sitting would show it. The bank has 5 on null handling and 60 across SQL.
Table Employees(emp_id, name, manager_id). The manager_id column is NULL for the CEO, who reports to no one. Which query lists employees who have no manager?
- 1SELECT * FROM Employees WHERE manager_id IS NULL;correct
- 2SELECT * FROM Employees WHERE manager_id <> NULL;
- 3SELECT * FROM Employees WHERE manager_id = NULL;
- 4SELECT * FROM Employees WHERE ISNULL(manager_id) = 0;
NULL is never equal (or unequal) to anything, including itself, so '= NULL' and '<> NULL' always evaluate to unknown and return no rows. IS NULL is the correct operator to test for a missing value. The ISNULL(manager_id) = 0 query actually checks for the opposite condition: MySQL's ISNULL() returns 1 for NULL values, so filtering on '= 0' keeps only the rows whose manager_id is NOT NULL.
Measure it
Reading answers tells you what’s true. A diagnostic tells you what you get wrong.
10 SQL questions across its topics, easy to hard, about fifteen minutes. You get a readiness figure with the arithmetic shown, the topics you missed named, and a practice set sized for today. Free: 1 diagnostic a month and 15 problems a day. No card.