intro
Let's look into how to recognize and work with NULL in SQL for effective data querying.
In SQL, NULL represents missing or unknown values in a database table. Unlike zero or an empty string, NULL signifies the absence of a value–not zero, not an empty string, but a true unknown or undefined value. This special characteristic means we need specific approaches to identify and handle NULL in SQL.
Handling NULL properly is essential for accurate queries, comparisons, and calculations.
Understanding NULL in SQL
When a column is set to NULL in a database, it means that no data has been entered for that field. This can occur for various reasons, such as optional fields in a form or unknown information.
Some of the key characteristics are as follows:
Now that we’ve had a fair idea of how to deal with NULL in SQL, it’s time to look into how to work with it.
How to Manage NULL in SQL: Example List
Let’s start by creating our example database structure.
Note: In this section, we are going to run the sample queries in DbVisualizer—the database client with the highest user satisfaction in the market. Any other database clients will do.
Run the query below to initialize a database where to apply the examples to deal with NULL in SQL:
1
CREATE TABLE departments (
2
department_id INT PRIMARY KEY,
3
department_name VARCHAR(50) NOT NULL,
4
manager_id INT
5
);
6
7
CREATE TABLE employees (
8
employee_id INT PRIMARY KEY,
9
first_name VARCHAR(50) NOT NULL,
10
last_name VARCHAR(50) NOT NULL,
11
department_id INT,
12
salary DECIMAL(10,2),
13
FOREIGN KEY (department_id) REFERENCES departments(department_id)
14
);
15
16
CREATE TABLE employee_contacts (
17
employee_id INT PRIMARY KEY,
18
primary_phone VARCHAR(15),
19
secondary_phone VARCHAR(15),
20
emergency_contact VARCHAR(15),
21
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
22
);
Launch the above query in DbVisualizer, and you will get:
Next, populate the tables with the sample data below:
1
INSERT INTO departments (department_id, department_name, manager_id) VALUES
2
(1, 'IT', 1),
3
(2, 'HR', NULL),
4
(3, 'Finance', 3),
5
(4, 'Marketing', NULL);
6
7
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
8
(1, 'Billie', 'Jane', 1, 60000),
9
(2, 'Jane', 'Vickers', 1, NULL),
10
(3, 'Bob', 'Stuttgart', 2, 45000),
11
(4, 'Alice', 'Williams', 2, 52000),
12
(5, 'Charles', 'Grace', 3, NULL),
13
(6, 'Diana', 'Prince', 4, 48000);
14
15
INSERT INTO employee_contacts (employee_id, primary_phone, secondary_phone, emergency_contact) VALUES
16
(1, '123-456-7890', NULL, '999-999-9999'),
17
(2, NULL, '234-567-8901', NULL),
18
(3, '345-678-9012', '456-789-0123', NULL),
19
(4, NULL, NULL, '567-890-1234');
This time, the result will be:
Time to look into the use cases!
Example #1 - Using IS NULL in SQL to find NULL Values
Imagine you’re managing an employee database for a company. The employees table stores details about each employee, including their salary. However, some employees have not yet been allocated a salary, leaving their salary column as NULL.
To guarantee payroll accuracy and proper compensation management, you need to find all employees who do not have a recorded salary. This is where the IS NULL condition becomes useful — allowing us to filter records where a specific column has a NULL value.
Now, let’s look into the query that will help us retrieve these employees without allocated salaries:
1
SELECT * FROM employees WHERE salary IS NULL;
The above query would produce:
It’s interesting how super cool it is to use IS NULL to identify missing salary records.
Example #2 - Using IS NOT NULL to Exclude NULL Values
Previously, we used IS NULL to identify employees who have not been assigned a salary. Now, let’s take the opposite approach—retrieving only those employees who do have a recorded salary.
In this case, we’ll use IS NOT NULL to exclude employees with NULL values in the salary column. That way, our results focus only on employees with assigned salaries, which is useful for payroll processing and salary analysis.
1
SELECT * FROM employees WHERE salary IS NOT NULL;
If we run the above query in DbVisualizer, we’re going to get this:
See how simple it is to exclude NULL values in our table? By using IS NOT NULL, we ensure that only employees with valid salary records are considered for salary-related reports.
Example #3 - Handling NULL in Aggregations
So far, we’ve filtered employees based on whether or not their salary is NULL , however, when performing aggregate calculations—such as finding the average salary or the total payroll cost—NULL values can impact the results.
By default, SQL aggregation functions like SUM() and AVG() tend to ignore NULL values, meaning unassigned salaries will not contribute to the calculation. However, if we need to count all employees, including those without a salary, we must handle NULL values appropriately using functions like COALESCE() or COUNT().
This example will show how NULL affects aggregation and how to account for it in queries.
1
SELECT AVG(salary) AS average_salary FROM employees;
Here, you notice that the NULL salaries are not included in the calculation. The query returned 51,250 when calculating the average salary across all employees, confirming that SQL automatically excludes NULL values when performing aggregate calculations.
Example #4 - Filtering Employees with Complex NULL Conditions
This example shows how to combine NULL checks with other conditions using OR.
Up to this point, we’ve handled NULL values using simple conditions like IS NULL and IS NOT NULL but it’s important to note that real-world queries often require more complex logic when dealing with NULL values.
For example, suppose we need to retrieve employees who either do not have an assigned salary (NULL) or earn more than 50,000. This type of condition is useful when analyzing high earners while also identifying those who still need a salary assignment.
To achieve that, we’ll use the OR operator to combine IS NULL with a salary threshold condition like this:
1
SELECT first_name, last_name, salary
2
FROM employees
3
WHERE salary IS NULL OR salary > 50000;
Combining NULL checks with other conditions helps us create more flexible queries that help identify missing data while extracting meaningful insights from salary distributions.
Example #5 - Data Reporting
When generating reports, it’s important to account for missing data to ensure accurate analysis. In this example, we’re creating a comprehensive department-wise employee report, focusing on salary data completeness.
Our query does the following:
1
SELECT
2
d.department_name,
3
COUNT(*) as total_employees,
4
COUNT(e.salary) as employees_with_salary,
5
COALESCE(AVG(e.salary), 0) as avg_salary,
6
CASE
7
WHEN COUNT(*) = COUNT(e.salary) THEN 'Complete Data'
8
ELSE 'Missing Salary Data'
9
END as data_status
10
FROM departments d
11
LEFT JOIN employees e ON d.department_id = e.department_id
12
GROUP BY d.department_name;
The above query produces this report:
The current approach produces employee reports are complete and accurate, allowing management to identify missing data and make informed decisions on salary allocations and workforce planning.
That’s all for this blog!
Conclusion
Handling NULL in SQL is a fundamental skill for anyone working with databases. NULL represents missing or unknown data, and it behaves differently from other values in SQL. By using operators like IS NULL and IS NOT NULL, we learned how to filter and manage NULL values in your queries.
Additionally, functions like COALESCE and IFNULL provide powerful tools for replacing NULL with default values, ensuring accurate calculations and comparisons.
As shown in this blog post, using a tool like DbVisualizer makes everything much easier. With just a few clicks, you can format, organize, and manage even the most intricate SQL queries. Get started and improve your knowledge base with our detailed user guide.
FAQ
What does NULL mean in SQL?
NULL in SQL represents a missing or unknown value. It is not the same as an empty string, zero, or any other value. It simply indicates the absence of data.
How do I check for NULL values in SQL?
Use the IS NULL operator to check for NULL values. For example:
1
SELECT * FROM employees WHERE salary IS NULL;
Why can't I use = NULL to check for NULL values?
In SQL, NULL is not equal to anything, not even itself. Comparisons involving NULL always return UNKNOWN. Instead, use IS NULL or IS NOT NULL.
How do I replace NULL with a default value in SQL?
Use the COALESCE or IFNULL function to replace NULL with a default value. For example:
1
SELECT COALESCE(salary, 0) FROM employees;
Does NULL affect SQL aggregations like SUM or AVG?
Yes, NULL values are ignored in SQL aggregations. For example, if you calculate the average salary using AVG(salary), rows with NULL salaries will not be included in the calculation.
