SQL NULL Functions

Overview

NULL values in SQL represent missing or unknown data. It's essential to handle NULL values properly to avoid unexpected results in your queries. SQL provides several functions to deal with NULL values effectively.

Common NULL Functions

Here are some common functions used to handle NULL values:

1. IS NULL

The IS NULL condition is used to check whether a value is NULL.

SELECT *
FROM Employees
WHERE ManagerID IS NULL;

This query retrieves all employees who do not have a manager (i.e., their ManagerID is NULL).

2. IS NOT NULL

The IS NOT NULL condition checks whether a value is not NULL.

SELECT *
FROM Employees
WHERE HireDate IS NOT NULL;

This query returns all employees who have a hire date recorded.

3. COALESCE()

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

SELECT EmployeeName,
    COALESCE(PhoneNumber, 'No Phone') AS Phone
FROM Employees;

This query retrieves employee names and their phone numbers. If the phone number is NULL, it displays 'No Phone' instead.

4. NULLIF()

The NULLIF() function returns NULL if the two expressions are equal; otherwise, it returns the first expression.

SELECT EmployeeName,
    NULLIF(Commission, 0) AS Commission
FROM Employees;

This query returns employee names and their commission. If the commission is 0, it returns NULL instead.

5. IFNULL() / ISNULL()

These functions return a specified value if the expression is NULL.

SELECT EmployeeName,
    IFNULL(Commission, 'No Commission') AS Commission
FROM Employees;

This query will display 'No Commission' for employees with a NULL commission value.

Example Usage

Here’s an example of how you can combine these functions in a query:

SELECT EmployeeName,
    COALESCE(PhoneNumber, 'No Phone') AS Phone,
    IFNULL(Commission, 'No Commission') AS Commission
FROM Employees
WHERE ManagerID IS NULL;

This query retrieves the names, phone numbers, and commissions of employees without managers, providing defaults for NULL values.

Conclusion

Handling NULL values is crucial for maintaining data integrity and accuracy in SQL queries. The functions mentioned above allow you to check for NULL values, replace them with meaningful defaults, and prevent unexpected results in your data retrieval.