SQL NULL Functions

SQL NULL functions are used to handle NULL values in database queries. Here are some common SQL NULL functions, along with examples and their output:

1. IS NULL and IS NOT NULL

   • IS NULL: Used to check if a value is NULL.
   • IS NOT NULL: Used to check if a value is not NULL.

   SELECT *
   FROM Employees
   WHERE ManagerID IS NULL;
   

   This query retrieves all employees who do not have a manager (where ManagerID is NULL).

   SELECT *
   FROM Employees
   WHERE ManagerID IS NOT NULL;
   

   This query retrieves all employees who have a manager (where ManagerID is not NULL).

2. COALESCE

   • Returns the first non-NULL value in a list.

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

   Example output:

   EmployeeID  PhoneNumber
   ----------- ------------
   1           123-456-7890
   2           No Phone Number
   3           987-654-3210
   

3. NULLIF

   • Returns NULL if the two arguments are equal, otherwise returns the first argument.

   SELECT NULLIF(Salary, 0) AS AdjustedSalary
   FROM Employees;
   

   Example output:

   AdjustedSalary
   ---------------
   50000
   NULL
   75000
   

4. IFNULL (MySQL specific) / ISNULL (SQL Server specific)

   • Returns the first argument if it is not NULL; otherwise, returns the second argument.

   -- MySQL
   SELECT EmployeeID, IFNULL(PhoneNumber, 'No Phone Number') AS PhoneNumber
   FROM Employees;
   
   -- SQL Server
   SELECT EmployeeID, ISNULL(PhoneNumber, 'No Phone Number') AS PhoneNumber
   FROM Employees;
   

   Example output:

   EmployeeID  PhoneNumber
   ----------- ------------
   1           123-456-7890
   2           No Phone Number
   3           987-654-3210
   

5. NVL (Oracle specific)

   • Similar to IFNULL/ISNULL, replaces NULL with the specified value.

   SELECT EmployeeID, NVL(PhoneNumber, 'No Phone Number') AS PhoneNumber
   FROM Employees;
   

   Example output:

   EmployeeID  PhoneNumber
   ----------- ------------
   1           123-456-7890
   2           No Phone Number
   3           987-654-3210
   

Example Data

Let's use the following example table Employees:

These SQL NULL functions are essential for handling missing data gracefully and ensuring queries return meaningful results.