Find Null Values in SQL with IS NULL

This guide shows you how to use the IS NULL operator in SQL to find rows where a column contains no value (NULL). You’ll learn the exact syntax, see practical examples, and avoid common pitfalls—all you need is a basic understanding of SQL queries and a database to practice on.

What You’ll Need

• A SQL database (MySQL, PostgreSQL, SQL Server, SQLite, etc.)
• A table with some NULL values in it (you can create a test table if needed)
• Access to run queries (command line, SQL client, or an IDE like DBeaver or DataGrip)

Step-by-Step

1. Understand what NULL means. In SQL, NULL is not zero, not an empty string, and not “false”. It represents an unknown or missing value. For example, a customer’s phone column might be NULL if they didn’t provide their number.
2. Write a basic SELECT query. Start with the columns you want to see. For example: SELECT first_name, last_name, phone FROM customers; This shows you the data, but NULLs are often displayed as blank or “NULL” depending on your client.
3. Add the WHERE clause with IS NULL. To filter for rows where a specific column is NULL, use: SELECT first_name, last_name, phone FROM customers WHERE phone IS NULL; This returns only rows where the phone column has no value.
4. Check for non-NULL values with IS NOT NULL. If you want the opposite—rows where the column has a value—use WHERE phone IS NOT NULL; This is handy when you need records that are complete.
5. Combine with other conditions. You can use AND/OR to combine IS NULL with other filters. For example: SELECT * FROM orders WHERE shipped_date IS NULL AND status = 'paid'; This finds paid orders that haven’t shipped yet.
6. Use IS NULL in JOINs or subqueries. For example, to find customers with no orders: SELECT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL; The LEFT JOIN returns NULL for the orders side when no match exists, so the WHERE clause picks those out.
7. Test with a simple example. If you’re learning, create a small table and insert a few rows with NULLs. For instance: CREATE TABLE test (id INT, name VARCHAR(50)); INSERT INTO test VALUES (1, 'Alice'), (2, NULL), (3, 'Bob'); Then run SELECT * FROM test WHERE name IS NULL; You should see only the row with id=2.

Common Mistakes

• Using = NULL instead of IS NULL. This is the most common error. WHERE phone = NULL will always return zero rows because NULL is not equal to anything, even itself.
• Using != NULL or <> NULL. Similarly, these never work. You must use IS NOT NULL.
• Forgetting that NULL in expressions propagates. If you do SELECT price * quantity and either column is NULL, the result is NULL, not 0. Be aware when calculating totals.
• Assuming empty string is the same as NULL. In most databases, '' is a value, not NULL. WHERE name IS NULL won’t match rows where name is an empty string. If you need to treat both as missing, use WHERE name IS NULL OR name = ''.
• Using IS NULL with aggregate functions incorrectly. Aggregate functions like COUNT(column) ignore NULLs, but COUNT(*) counts all rows. This can lead to confusion when counting non-NULL values.

Tips

• Use IS NOT NULL to filter for complete records. For example, before sending emails, you might want WHERE email IS NOT NULL.
• Combine with COALESCE for display. If you want to show a placeholder instead of NULL, use SELECT COALESCE(phone, 'N/A') AS phone FROM customers; This doesn’t change the data, just the output.
• Remember that NULL in ORDER BY. By default, NULLs sort first in ascending order (on most databases). If you want them last, use ORDER BY phone NULLS LAST (PostgreSQL, Oracle) or ORDER BY phone IS NULL, phone (MySQL, SQL Server).
• Use IS NULL in UPDATE or DELETE carefully. For example, DELETE FROM logs WHERE deleted_at IS NOT NULL; is a common way to purge soft-deleted rows. Always test with SELECT first.
• Index columns that you frequently check for NULL. In some databases, a partial index like CREATE INDEX idx_phone ON customers(phone) WHERE phone IS NULL; can speed up queries, but this is advanced and database-specific.

Troubleshooting

• Query returns no rows but you expect some. Double-check that the column actually contains NULL, not an empty string or zero. Run SELECT * FROM table and look at the raw data. If you see blank cells, they might be empty strings, not NULL.
• Query returns rows with empty strings. If you want to include those, modify the condition to WHERE column IS NULL OR column = ''.
• Getting a syntax error. Make sure you wrote IS NULL (two words) and not ISNULL (which is a function in SQL Server). Also check that you have the correct column name and that it exists in the table.
• NULL in a JOIN condition. If you join on a column that contains NULLs, those rows won’t match because NULL = NULL is not true. Use ON a.col = b.col OR (a.col IS NULL AND b.col IS NULL) if you need to match NULLs, but this is rare and often indicates a design issue.
• Performance is slow. If your table is large and you’re filtering on IS NULL, ensure the column is indexed. Without an index, the database scans the whole table. Create an index on the column if you run this query often.

Frequently Asked Questions

Why doesn’t WHERE column = NULL work?

Because NULL is not a value; it’s a marker for missing data. In SQL, any comparison with NULL (like =, <, >) results in UNKNOWN, which is treated as false in WHERE clauses. That’s why you must use IS NULL.

What’s the difference between NULL and empty string?

NULL means no value was provided. An empty string (”) is a value that happens to be zero characters long. They are different: NULL is unknown, while ” is known but empty. Depending on your application, you might treat them the same or differently.

Can I use IS NULL with multiple columns?

Yes, you can combine conditions: WHERE col1 IS NULL OR col2 IS NULL finds rows where at least one is NULL. Use AND if you need both to be NULL.

Does COUNT(column) include NULLs?

No. COUNT(column) counts only non-NULL values. COUNT(*) counts all rows. If you want to count NULLs, use COUNT(*) - COUNT(column) or SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END).

How do I replace NULL with a default value in a query?

Use the COALESCE function: SELECT COALESCE(column, 'default') FROM table; This returns ‘default’ when column is NULL. In SQL Server, you can also use ISNULL(column, ‘default’), but COALESCE is standard and supports more than two arguments.

Is IS NULL faster than IS NOT NULL?

Not inherently. Performance depends on indexing and data distribution. If most rows have NULL, an index on the column might help IS NULL queries. In practice, the difference is usually negligible unless you’re dealing with millions of rows.

Related

• Credit Card vs Debit Card: Key Differences
• Samsung Voice Recorder App Not Working? Fix It
• Fix iOS 26 Green Screen Issue: Step-by-Step