Blog

Introduction to SQL NULL Functions

In SQL, NULL represents a missing or unknown value. Handling NULL values properly is essential for accurate data manipulation and querying. SQL provides several functions to work with NULL values efficiently, including IS NULL, IS NOT NULL, COALESCE, and NULLIF.

1. IS NULL and IS NOT NULL

IS NULL:

This function checks if a column or expression contains a NULL value.

SELECT * FROM table_name WHERE column_name IS NULL;

This query retrieves all rows from table_name where column_name contains NULL values.

IS NOT NULL:

This function checks if a column or expression does not contain a NULL value.

SELECT * FROM table_name WHERE column_name IS NOT NULL;

This query retrieves all rows from table_name where column_name does not contain NULL values.

2. COALESCE

The COALESCE function returns the first non-NULL value in a list of expressions.

SELECT COALESCE(column_name, default_value) AS alias FROM table_name;

• column_name: The column to evaluate.
• default_value: The value to return if column_name is NULL.
• alias: An optional alias for the result column.

Example:

Let’s say we have a table employees with columns name and salary. We want to retrieve the salary, replacing NULL values with 0.

SELECT name, COALESCE(salary, 0) AS salary FROM employees;

This query will return all employee names along with their salaries. If the salary is NULL, it will be replaced with 0.

3. NULLIF

The NULLIF function compares two expressions and returns NULL if they are equal.

SELECT NULLIF(expression1, expression2) AS alias;

• expression1: The first expression to compare.
• expression2: The second expression to compare.
• alias: An optional alias for the result.

Example:

Suppose we have a table students with columns id and age. We want to display the age of students, but if their age is 20, we want to display NULL instead.

SELECT id, NULLIF(age, 20) AS adjusted_age FROM students;

This query will return the ID of each student along with their age. If the age is 20, it will be displayed as NULL.

Conclusion

Understanding and utilizing NULL functions in SQL is crucial for handling missing or unknown data effectively. With IS NULL, IS NOT NULL, COALESCE, and NULLIF, you can manipulate NULL values to suit your data needs, ensuring accurate results in your queries.