SQL NULL Values

Getting Started with SQL Server

About Lesson

SQL NULL Values

In SQL, a NULL value represents a missing or undefined value. It is important to understand how to handle NULL values in your queries because they can affect the results of your SQL operations.

Checking for NULL Values

To check for NULL values, you can use the IS NULL and IS NOT NULL operators.

Example 1: Checking for NULL Values

SELECT first_name, last_name
FROM employees
WHERE department IS NULL;

This query selects the first and last names of employees whose department is NULL.

Example 2: Checking for Non-NULL Values

SELECT first_name, last_name
FROM employees
WHERE department IS NOT NULL;

This query selects the first and last names of employees whose department is not NULL.

Handling NULL Values in Expressions

When performing calculations or concatenating strings, NULL values can cause unexpected results. You can use functions like COALESCE or IFNULL to handle NULL values.

Example 3: Using COALESCE

SELECT first_name, last_name, COALESCE(department, 'Unknown') AS department
FROM employees;

This query selects the first and last names of employees and their department. If the department is NULL, it returns ‘Unknown’ instead.

Example 4: Using IFNULL (MySQL)

SELECT first_name, last_name, IFNULL(department, 'Unknown') AS department
FROM employees;

This query performs the same operation as the previous example but uses the IFNULL function available in MySQL.

Aggregating NULL Values

When using aggregate functions like COUNT, SUM, AVG, etc., NULL values are ignored, except when using COUNT(*).

Example 5: COUNT with NULL Values

SELECT COUNT(department) AS department_count
FROM employees;

This query counts the number of non-NULL department values in the “employees” table.

Example 6: COUNT(*) Including NULL Values

SELECT COUNT(*) AS total_employees
FROM employees;

This query counts all rows in the “employees” table, including those with NULL values.

Understanding how to work with NULL values is crucial for accurate data querying and manipulation in SQL. Proper handling of NULL values ensures that your queries return the expected results.