SQL NULL Functions

The SQL NULL Functions

There are several SQL NULL functions and operators that you can use to work with NULL values, which represent missing or unknown data in sql.

Handling NULL values is important in database queries to ensure accurate results and avoid errors. SQL IFNULL(), ISNULL(), COALESCE(), and NVL() functions

Here, we will learn SQL NULL functions and operators with examples.

Examples of SQL NULL functions

1. IS NULL Operator or ISNULL():
   In SQL, the IFNULL() function (also known as ISNULL() in some database systems) is used to handle NULL values by replacing them with a specified value or
   expression.

To find out if a column has NULL values in it, use the IS NULL operator.

SQL> SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE MiddleName IS NULL;

IS NULL MiddleName examines each row to see if the MiddleName column is NULL.

For MySQL example:
If an expression in MySQL is NULL, you can use the IFNULL() function to return a different value.

SQL> SELECT CoconutsName, UnitPrice * (UnitsIn + IFNULL(UnitsOut, 0))
FROM Coconuts;

Or For COALESCE() functions example

SQL> SELECT CoconutsName, UnitPrice * (UnitsIn + COALESCE(UnitsOut, 0))
FROM Coconuts;

For SQL server
If an expression is NULL, the SQL Server IFNULL() function allows you to return an alternate value.

SQL> SELECT CoconutsName, UnitPrice * (UnitsIn + IFNULL(UnitsOut, 0))
FROM Coconuts;

Or For COALESCE() funtions example

SQL> SELECT CoconutsName, UnitPrice * (UnitsIn + COALESCE(UnitsOut, 0))
FROM Coconuts;

2. IS NOT NULL Operator:
   To determine whether a column contains NULL values or not, utilize the IS NOT NULL operator.

SQL> SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE MiddleName IS NOT NULL;

MiddleName IS NOT NULL checks if the MiddleName column is not NULL for each row.

3. COALESCE Function:
   The COALESCE function is used to return the first non-NULL value in a list of expressions.

SQL> SELECT OrderID, COALESCE(ActualShipDate, EstimatedShipDate) AS ShipDate
FROM Orders;

COALESCE(ActualShipDate, EstimatedShipDate) returns the first non-NULL value between “ActualShipDate” and “EstimatedShipDate” for each row.

4. NULLIF Function:
   The NULLIF function is used to return NULL if two expressions are equal; otherwise, it returns the first expression.

SQL> UPDATE Products
SET Discount = NULLIF(Discount, 0)
WHERE Discount = 0;

NULLIF(Discount, 0) returns NULL if the “Discount” column is equal to 0; otherwise, it returns the value of “Discount.”

These SQL NULL functions and operators are essential for working with missing or unknown data in your database.

They help you filter, transform, and handle NULL values effectively in your queries, ensuring accurate results and preventing errors.

5. NVL() Function:
   In Oracle Database, the NVL() function is used to handle NULL values.

SQL> SELECT EmployeeID, FirstName, NVL(MiddleName, ‘N/A’) AS MiddleName
FROM Employees;

When the MiddleName column is NULL, NVL(MiddleName, ‘N/A’) returns ‘N/A’ as the default value; if not, it returns the value of “MiddleName.”

It’s easy to remember that the NVL() method is exclusive to Oracle SQL and SQL Server; for comparable tasks, try ISNULL() or COALESCE() instead.