SQL NULL Functions

 In SQL, NULL represents the absence of a value or an unknown value. There are several SQL functions used to handle NULL values in queries. Below are some of the most common SQL NULL functions:

1. IS NULL and IS NOT NULL

These are used to check if a value is NULL or not.

• IS NULL: Checks if a value is NULL.
• IS NOT NULL: Checks if a value is not NULL.

Example:

SELECT * FROM employees WHERE department IS NULL;

2. COALESCE()

This function returns the first non-NULL expression from a list of expressions. If all the expressions evaluate to NULL, it returns NULL.

Syntax:

COALESCE(expr1, expr2, ..., exprN)

Example:

SELECT COALESCE(phone_number, 'No Phone') FROM customers;

This returns the phone_number if it is not NULL, otherwise, it returns 'No Phone'.

3. IFNULL() or NVL()

Both are used to replace NULL with a specified value. The difference is mostly in the specific SQL dialect being used (e.g., MySQL uses IFNULL(), Oracle uses NVL()).

• MySQL Example:

  SELECT IFNULL(phone_number, 'No Phone') FROM customers;
  

• Oracle Example:

  SELECT NVL(phone_number, 'No Phone') FROM customers;
  

4. NULLIF()

This function returns NULL if two expressions are equal. Otherwise, it returns the first expression.

Syntax:

NULLIF(expr1, expr2)

Example:

SELECT NULLIF(salary, 0) FROM employees;

This will return NULL if salary is 0; otherwise, it returns the salary.

5. CASE (with NULL checking)

The CASE expression can be used for conditional logic, including checking for NULL.

Example:

SELECT 
  CASE 
    WHEN salary IS NULL THEN 'Salary Not Provided'
    ELSE salary 
  END AS SalaryInfo
FROM employees;

6. ISNULL() (SQL Server)

Similar to IFNULL() in MySQL and NVL() in Oracle, SQL Server uses ISNULL() to replace NULL with a specified value.

Example:

SELECT ISNULL(phone_number, 'No Phone') FROM customers;

7. LEAST() and GREATEST()

These functions return the smallest or largest value in a list of expressions. They can be used to handle NULL values by treating NULL as a smaller value in LEAST() and as a larger value in GREATEST().

Example:

SELECT LEAST(salary, bonus, 5000) FROM employees;

Summary:

• IS NULL / IS NOT NULL: Check if a value is NULL.
• COALESCE(): Return the first non-NULL value.
• IFNULL() / NVL() / ISNULL(): Replace NULL with a specified value.
• NULLIF(): Return NULL if two expressions are equal.
• CASE: Conditional logic with NULL checking.