SQL Null Functions
In SQL, null is a special marker that represents the absence of a value or an unknown value. It is not the same as zero or an empty string. SQL provides several functions and operators to work with null values. Here are some commonly used functions for handling null values in SQL:
- IS NULL: This operator checks whether a value is null. Example:sql
SELECT * FROM employees WHERE department IS NULL;
IS NOT NULL: This operator checks whether a value is not null. Example:
sql
SELECT * FROM employees WHERE department IS NOT NULL;
COALESCE: This function returns the first non-null expression in the argument list. Example:
sql
SELECT COALESCE(department, 'Unknown') FROM employees;
NULLIF: This function returns null if two expressions are equal; otherwise, it returns the first expression. Example:
sql
SELECT NULLIF(department, 'Unknown') FROM employees;
NVL: This function returns a substitute value if an expression is null. Example:
sql
SELECT NVL(department, 'Unknown') FROM employees;
IFNULL: This function returns the second expression if the first expression is null; otherwise, it returns the first expression. Example:
sql
SELECT IFNULL(department, 'Unknown') FROM employees;
CASE statement: The CASE statement allows you to perform conditional operations based on null values. Example:
sql
SELECT CASE WHEN department IS NULL THEN 'Unknown' ELSE department END FROM employees;
These functions and operators can be used to handle and manipulate null values in SQL queries, allowing you to control the flow of data and handle cases where values are missing or unknown.