In SQL, NULL represents the absence of a value or unknown data. It is not the same as 0 or an empty string. A column can have a NULL value if no data is provided for it. When working with NULL in SQL, it’s important to note that NULL is not equal to anything, not even another NULL. This means that you cannot use = or != to check for NULL values. Instead, you must use the IS NULL or IS NOT NULL operators.
IS NULL in SQL
This query retrieves all employees who do not have a manager assigned (manager_id is missing).
Practice: Select all the rows from the salaries table which do not have an end_date.
IS NOT NULL in SQL
This query retrieves all employees who have a manager assigned (manager_id is not missing).
Practice: Select all the rows from the projects table which do have an end_date.
NULL Comparison
Since NULL cannot be directly compared with standard operators, SQL provides functions to replace or handle NULL values in queries.
IFNULL in SQL
The IFNULL(expression, replacement) function returns the replacement value if the expression is NULL.
SELECT first_name, IFNULL(manager_id, 0) FROM employees;
This query returns each employee’s first name and their manager ID. If the manager_id is NULL, it will display 0 instead.
Practice: Check on all the rows of the salaries table which do not have an end_date and replace it with January 10, 2026 using IFNULL.
COALESCE in SQL
The COALESCE() function returns the first non-NULL value from a list of expressions.
This query returns the manager ID for each employee. If the manager_id is NULL, it replaces it with 999.
Practice: Check on all the rows of the salaries table which do not have an end_date and replace it with January 10, 2026 using COALESCE.
NULLIF in SQL
The NULLIF(expression1, expression2) function returns NULL if both expressions are equal; otherwise, it returns the first expression.
This query returns the first_name of each employee along with department_id. If the department_id is 0, the NULLIF() function returns NULL instead of 0 but if the department_id is not 0, it returns the actual value.
Practice: Write a query that checks amount of salary equal to 70,000. If both are equal, it must return NULL, otherwise it must return the amount.
FAQs
1. What is the difference between NULL and 0 in SQL?
NULL means the value is unknown or missing, while 0 is an actual numeric value. For example, a salary column with NULL means “salary not entered,” whereas a salary of 0 means “salary is zero.”
2. Why can’t we use = or != to compare NULL values in SQL?
In SQL, NULL represents an unknown value, so comparing it with = or != always returns false. Instead, you must use IS NULL or IS NOT NULL to check for NULL values.
3. What is the difference between IFNULL and COALESCE in SQL?
IFNULL(expression, replacement) returns the replacement if the expression is NULL.
COALESCE(expr1, expr2, …) returns the first non-NULL value from a list, making it more flexible.
4. When should I use NULLIF in SQL?
Use NULLIF when you want to return NULL instead of a specific value. For example, NULLIF(department_id, 0) turns department_id = 0 into NULL, which can simplify calculations and reports.
5. How does SQL treat NULL in aggregate functions like COUNT, SUM, or AVG?
Most aggregate functions ignore NULL values:
COUNT(column) ignores NULLs and only counts non-NULL values.
SUM(column) and AVG(column) skip NULLs in their calculations.
If you want to include NULLs, you must replace them with a default value using COALESCE or IFNULL.