NULL in SQL: Why = NULL Never Works (IS NULL and COALESCE)
Equals null never finds the empty values in SQL. See why, use IS NULL and IS NOT NULL instead, spot how nulls hide from not-equal filters, and fill the gaps with COALESCE, on a sample online shop database.
Step by step
Missing values
- Why equals null fails. Some customers never gave an email address. WHERE email = NULL looks like it should find them, but it never does. Here is why, and the 2 things that work: IS NULL and COALESCE.
- The customers table. The examples use the customers table from a sample online shop. It has 40 customers, each with an email and a city, but a few of those were left empty (NULL).
The trap
- The trap. First, the query most people try: SELECT first_name, last_name, email FROM customers WHERE email = NULL; It returns 0 rows. There is no error, just an empty result, even though 2 customers have no email. Nothing warns you that the query is wrong.
SELECT first_name, last_name, email FROM customers WHERE email = NULL;
IS NULL
- IS NULL. NULL isn't a value. It means unknown, and nothing equals unknown, not even another NULL. So replace = NULL with IS NULL. Now the query returns the 2 customers with no email. This makes a quick data quality check.
IS NULL - IS NOT NULL. For the opposite, such as a mailing list, change IS NULL to IS NOT NULL. It keeps every row where email has a value. It returns 38 customers. 38 plus 2 is 40, so every customer is counted exactly once.
IS NOT NULL
Hidden nulls
- Nulls hide. NULLs also hide from other filters. To find customers who are not in Leeds, write WHERE city <> 'Leeds'. The <> sign means not equal. It returns 34 rows. 5 of the 40 customers live in Leeds, so you would expect 35. The customer with no city is missing, because NULL <> 'Leeds' is unknown, not true. Add OR city IS NULL to keep that row.
SELECT first_name, city FROM customers WHERE city <> 'Leeds';
COALESCE
- COALESCE. To fill gaps in a report, use COALESCE. It returns the first value in its list that isn't NULL. COALESCE(email, 'No email') shows No email when the email is empty, and COALESCE(city, 'Unknown') does the same for city. The query returns the 11 customers in the East region with no blank cells. One row shows No email and one shows Unknown. The table itself doesn't change, only the output.
SELECT first_name, COALESCE(email, 'No email') AS email, COALESCE(city, 'Unknown') AS city FROM customers WHERE region = 'East';
Recap
- Null in four rules. In short: NULL means unknown, so = NULL never matches. Use IS NULL and IS NOT NULL. Check for rows that quietly drop out of <> filters. Use COALESCE to show a default instead of a blank.
Common mistakes
- Writing = NULL or <> NULL. Both return no rows and give no error, so the mistake is easy to miss. Use IS NULL or IS NOT NULL instead.
- Expecting <> to include empty values. WHERE city <> 'Leeds' leaves out rows where city is NULL. If you want them too, write WHERE city <> 'Leeds' OR city IS NULL.
- Treating empty text as NULL. An empty text value ('') is a real value, so IS NULL won't find it (Oracle is the exception, because it stores empty text as NULL). To catch both, use WHERE email IS NULL OR email = ''.
- Using COUNT(email) to count customers. COUNT(email) skips rows where email is NULL, so here it gives 38, not 40. Use COUNT(*) to count every row.
When a business analyst uses this
Before a data migration, a business analyst runs SELECT ... WHERE email IS NULL on the customer table, logs the count of missing emails as an issue in the RAID log, and uses COALESCE(email, 'No email') in the review extract so stakeholders see the gaps clearly at sign-off instead of blank cells.
Questions people ask
What does NULL mean in SQL?
NULL means a value is missing or unknown. It isn't 0 and it isn't empty text, so = and <> can't find it.
How do you use IS NULL in a WHERE clause?
Put IS NULL after the column name, as in WHERE email IS NULL. Use WHERE email IS NOT NULL to get the rows that have a value.
Does IS NULL work in SQL Server?
Yes. IS NULL, IS NOT NULL and COALESCE work in SQL Server, and also in MySQL, PostgreSQL, Oracle and SQLite. SQL Server also has its own ISNULL function, which takes only 2 values.
