Null Functions in SQL

Introduction

• Null values in SQL are special values used to represent unknown, missing, or not applicable data.
• Null in SQL is not equivalent to zero or blank; it is a distinct data type.

Importance of Null Functions

• Null functions are specialized functions that perform operations on null values within database tables.

Types of Null Functions

1. IS NULL Function

• Purpose: Checks whether a value is null and returns a Boolean.
• Example Usage:
  • Query: SELECT salary, ISNULL(salary) AS nullVal FROM employee;
  • Output: Returns 0 if the salary is not null and 1 if it is null.
• Advanced Usage (aggregate example):
  • Query: SELECT SUM(ISNULL(salary, 10000)) AS Sal_sum FROM employee;
  • If salary is null, adds 10000 for summation purposes.

2. COALESCE Function

• Purpose: Returns the first non-null value from a list of expressions.
• Example Usage:
  • Query: SELECT ID, salary, COALESCE(salary, ID) AS result FROM employee;
  • Behavior: If salary is not null, it returns salary; otherwise, it returns ID.

3. NULLIF Function

• Purpose: Returns null if two expressions are equal, otherwise returns the first expression.
• Example Usage:
  • Query: SELECT ID, first_name, NULLIF(ID, new_ID) AS result FROM employee;
  • Behavior: If ID equals new_ID, returns null; otherwise, returns ID.

4. IFNULL Function

• Purpose: Replaces null values with a specified value.
• Example Usage:
  • Query: SELECT ID, salary, IFNULL(salary, 999) AS result FROM employee;
  • Behavior: Returns salary if not null; if null, replaces with 999.

Conclusion

• These null functions (IS NULL, COALESCE, NULLIF, IFNULL) are useful for handling null values in SQL databases.
• It is important to check database documentation for support of these functions before implementation.

Disclaimer

• Always verify whether a null function is supported by the particular database software being used.

Closing Remarks

• This session was aimed at providing a comprehensive overview of managing null values using SQL functions.