SQL NULL Functions

SQL provides several functions to handle NULL values. These functions help manage and manipulate NULL values in your database queries.

Common SQL NULL Functions

• IS NULL
• IS NOT NULL
• COALESCE()
• NULLIF()
• IFNULL() (MySQL)
• NVL() (Oracle)

Example: IS NULL and IS NOT NULL

SELECT employee_id, first_name, last_name
FROM Employees
WHERE middle_name IS NULL;

Example: COALESCE()

SELECT employee_id, first_name, last_name, COALESCE(middle_name, 'N/A') AS middle_name
FROM Employees;

Example: NULLIF()

SELECT product_id, product_name, 
NULLIF(discount_price, 0) AS discount_price
FROM Products;

Example: IFNULL() (MySQL)

SELECT product_id, product_name, 
IFNULL(discount_price, 0) AS discount_price
FROM Products;

Example: NVL() (Oracle)

SELECT product_id, product_name, 
NVL(discount_price, 0) AS discount_price
FROM Products;

Sample Tables

Consider the following Employees table:

Employees:
| employee_id | first_name | middle_name | last_name |
|-------------|------------|-------------|-----------|
| 1           | John       | NULL        | Doe       |
| 2           | Jane       | A.          | Smith     |
| 3           | Michael    | NULL        | Johnson   |

Result of IS NULL Query

After running the IS NULL example query, the result would look like this:

| employee_id | first_name | last_name |
|-------------|------------|-----------|
| 1           | John       | Doe       |
| 3           | Michael    | Johnson   |

Important Notes

• Use IS NULL and IS NOT NULL to check for NULL values.
• The COALESCE() function returns the first non-NULL value in the list.
• The NULLIF() function returns NULL if the two arguments are equal.
• Use IFNULL() in MySQL and NVL() in Oracle to replace NULL with a specified value.