NULL Values in MySQL

In MySQL, NULL represents missing, unknown, or undefined data. It is not the same as an empty string ('') or zero (0).

1. Checking for NULL Values

✅ Use IS NULL to check if a value is NULL:

SELECT * FROM employees WHERE department IS NULL;

✅ Use IS NOT NULL to check if a value is NOT NULL:

SELECT * FROM employees WHERE salary IS NOT NULL;

🚫 DO NOT use = NULL because it always returns FALSE!

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

2. Handling NULL in Queries

IFNULL() – Replace NULL with Default Value

SELECT name, IFNULL(department, 'Unknown') AS dept FROM employees;

✅ Replaces NULL department values with 'Unknown'.

COALESCE() – Return First Non-NULL Value

SELECT name, COALESCE(department, 'No Dept', 'N/A') AS dept FROM employees;

✅ Returns the first non-NULL value from the list.

NULLIF() – Returns NULL if Two Values Are Equal

SELECT NULLIF(5000, 5000) AS result;  -- Returns NULLSELECT NULLIF(6000, 7000) AS result;  -- Returns 6000

✅ Useful for avoiding duplicate values in comparisons.

3. NULL in Aggregate Functions

✅ MySQL ignores NULLs in aggregation functions:

SELECT AVG(salary) FROM employees;  -- NULL salaries are ignoredSELECT COUNT(salary) FROM employees;  -- Counts only non-NULL values

4. NULL in Sorting (ORDER BY)

✅ Sort NULL values last:

SELECT * FROM employees ORDER BY salary IS NULL, salary ASC;

5. Counting NULL Values

SELECT COUNT(*) - COUNT(salary) AS null_count FROM employees;

✅ Finds how many NULL salaries exist.

6. NULL in JOIN and WHERE

✅ Be careful when using JOIN with NULL values:

SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;

🚀 If there's no match, department_name will be NULL.

7. Key Takeaways

✅ NULL means missing/unknown data, NOT zero or empty string.
✅ Use IS NULL or IS NOT NULL to filter NULL values.
✅ Use IFNULL(), COALESCE(), and NULLIF() to handle NULLs.
✅ Aggregate functions ignore NULL values.