Null Functions in MySql
NULL Functions in MySQL
MySQL provides several functions to handle NULL values in queries. NULL represents missing or unknown data.
1. Common NULL Functions
| Function | Description |
|---|---|
IS NULL | Checks if a value is NULL |
IS NOT NULL | Checks if a value is NOT NULL |
IFNULL(expr, replacement) | Returns replacement if expr is NULL |
COALESCE(expr1, expr2, ..., exprN) | Returns the first non-NULL value from the list |
NULLIF(expr1, expr2) | Returns NULL if expr1 = expr2, otherwise returns expr1 |
2. Example Table: employees
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | IT | 6000 |
| 2 | Bob | HR | 7000 |
| 3 | Charlie | NULL | 5000 |
| 4 | David | IT | NULL |
3. Checking for NULL (IS NULL, IS NOT NULL)
SELECT * FROM employees WHERE department IS NULL;
✅ Finds employees without a department.
SELECT * FROM employees WHERE salary IS NOT NULL;
✅ Finds employees with a salary.
4. IFNULL() – Replace NULL with Default Value
SELECT name, IFNULL(department, 'Unknown') AS dept FROM employees;
✅ Output:
+---------+-----------+| name | dept |+---------+-----------+| Alice | IT || Bob | HR || Charlie | Unknown || David | IT |+---------+-----------+
🚀 If department is NULL, it is replaced with 'Unknown'.
5. 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.
SELECT COALESCE(NULL, NULL, 'Fallback', 'Default') AS result;
✅ Output: 'Fallback' (First non-NULL value is chosen.)
6. NULLIF() – Returns NULL if Two Values are Equal
SELECT NULLIF(5000, 5000) AS result;
✅ Output: NULL (Because 5000 = 5000)
SELECT NULLIF(6000, 7000) AS result;
✅ Output: 6000 (Because 6000 ≠ 7000)
7. Handling NULL in Aggregation (SUM(), AVG(), etc.)
MySQL ignores NULL values in aggregate functions.
SELECT AVG(salary) FROM employees;
✅ Ignores NULL salaries when calculating the average.
8. Sorting and Filtering NULL Values
Order NULLs Last
SELECT * FROM employees ORDER BY salary IS NULL, salary ASC;
✅ Places NULL values at the bottom.
Count NULL Values
SELECT COUNT(*) - COUNT(salary) AS null_count FROM employees;
✅ Finds how many NULL salaries exist.
9. Key Takeaways
✅ Use IS NULL and IS NOT NULL to filter NULL values.
✅ Use IFNULL() and COALESCE() to replace NULL with default values.
✅ Use NULLIF() to return NULL when two values match.
✅ Aggregate functions ignore NULLs automatically.
