Understanding SQL NULLs: Common Pitfalls and How to Handle Them

When working with databases, you will often encounter NULLs. NULL represents missing or unknown data in SQL. Understanding how NULLs behave and how to work with them is essential for avoiding common mistakes and writing correct SQL queries.

One of the most important things to know about NULLs is that NULL is not the same as zero or an empty string. It means "no value." Because of this, SQL treats NULL differently in comparisons.

For example, using the equals (=) operator with NULL does not work as you might expect. The comparison will not return TRUE, even if you compare NULL to NULL. Instead, it returns UNKNOWN.

-- This query will NOT return rows where value is NULL
SELECT * FROM my_table WHERE value = NULL;

The correct way to check for NULL is to use the IS NULL or IS NOT NULL operators.

-- Find rows where value is NULL
SELECT * FROM my_table WHERE value IS NULL;

-- Find rows where value is NOT NULL
SELECT * FROM my_table WHERE value IS NOT NULL;

Another common pitfall is using aggregate functions like COUNT, SUM, or AVG. These functions often ignore NULLs in the columns they process.

-- Suppose salary has NULL values
SELECT COUNT(salary) AS count_non_null, COUNT(*) AS total_rows FROM employees;

In this example, COUNT(salary) counts only non-NULL salaries, while COUNT(*) counts all rows regardless of NULLs.

Additionally, NULL values affect logical operations. For example, when using AND, OR, or NOT, the presence of NULL can lead to UNKNOWN results that might filter out rows unintentionally.

-- Example
SELECT * FROM products WHERE price > 100 AND discount IS NULL;

If discount is NULL, the condition discount = NULL will fail but discount IS NULL will work correctly.

To replace NULLs with a default value in query results, use the COALESCE function. It returns the first non-NULL value from its arguments.

-- Replace NULL with 0 in sales column
SELECT product_id, COALESCE(sales, 0) AS sales FROM sales_data;

In conclusion, always remember: use IS NULL or IS NOT NULL to check for NULL values, handle NULLs carefully in conditions and aggregates, and use COALESCE to provide fallback values. This will help you avoid common SQL NULL pitfalls and write better queries.