SQL NULL Handling

What is NULL?

NULL represents missing or unknown data.

It is different from 0 or an empty string.

Checking for NULL

SELECT *
FROM employees
WHERE salary IS NULL;

Checking for NOT NULL

SELECT *
FROM employees
WHERE salary IS NOT NULL;

Incorrect vs Correct Comparison

-- Incorrect
SELECT * FROM employees WHERE salary = NULL;

-- Correct
SELECT * FROM employees WHERE salary IS NULL;

Using COALESCE()

SELECT name, COALESCE(salary, 0) AS salary
FROM employees;

Multiple Values in COALESCE

SELECT COALESCE(NULL, NULL, 100, 200);

NULL in Aggregates

SELECT AVG(salary)
FROM employees;

NULL in Calculations

-- Problem
SELECT salary + bonus
FROM employees;

-- Fix
SELECT COALESCE(salary, 0) + COALESCE(bonus, 0)
FROM employees;

Full Example

SELECT name,
       COALESCE(salary, 0) AS salary
FROM employees
WHERE salary IS NOT NULL;

Why NULL Handling is Important

• Handle missing data
• Prevent incorrect calculations
• Improve data quality
• Essential for analysis

Common Mistakes

• Using = NULL instead of IS NULL
• Ignoring NULL in calculations
• Not using COALESCE

Practice

Replace NULL values and filter non-null rows.

SELECT column_name,
       COALESCE(column_name, 0)
FROM table_name
WHERE column_name IS NOT NULL;

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic