Handling NULL Values with Functions

In SQL, NULL represents a missing or unknown value. Standard comparisons don't work on NULL, so SQL provides a set of specialized functions to handle these cases, allowing you to substitute a default value when a NULL is encountered. The main functions for this are COALESCE(), ISNULL(), IFNULL(), and NVL(). While they achieve similar goals, their syntax and availability differ across database systems.

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Discount DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Price, Discount)
VALUES (1, 'Laptop', 1200.00, 0.10),
       (2, 'Mouse', 25.00, NULL),
       (3, 'Keyboard', 75.00, 0.05),
       (4, 'Monitor', 300.00, NULL);

Database Overview & Sample Data

The database is pre-loaded with the Products table. Run the query below to explore the data.

Products Table

SELECT * FROM Products;

1. COALESCE() (Standard SQL)

The COALESCE() function is the most standard and versatile of the group. It is supported by almost all major SQL databases (including PostgreSQL, SQL Server, MySQL, Oracle, and SQLite).

It accepts a list of arguments and returns the first non-NULL value it encounters.

Operation: Display all products and their discounts. If a discount is missing (NULL), show 0.00 instead.

Query:

SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

Explanation: For each row, COALESCE() checks the Discount column. If it's not NULL, it returns the discount value. If it is NULL, it moves to the next argument, 0.00, and returns that.

Result:

2. ISNULL() (SQL Server)

The ISNULL() function is specific to Microsoft SQL Server. It takes two arguments and returns the first one if it's not NULL, otherwise it returns the second.

Query (SQL Server Syntax):

-- Note: SQLite uses COALESCE / IFNULL for this example
SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

3. IFNULL() (MySQL & SQLite)

The IFNULL() function is specific to MySQL and SQLite. It is functionally identical to ISNULL() in SQL Server, taking two arguments and returning the first if it's not NULL, otherwise the second.

Query:

SELECT ProductName, Price, IFNULL(Discount, 0.00) AS EffectiveDiscount
FROM Products;

4. NVL() (Oracle)

The NVL() function is specific to Oracle databases. It also takes two arguments and returns the second if the first is NULL.

Query (Oracle Syntax):

-- Note: SQLite uses COALESCE for this example
SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

Summary and Best Practice

Best Practice: Whenever possible, use COALESCE(). It is the most portable function across different database systems and offers more flexibility by allowing more than two arguments.

Hands-on Exercise

Task: Select the ProductName and a calculated column named FinalPrice which is the Price minus the Discount. If the Discount is NULL, use 0.00 as the discount value.

Your Query:

-- Write your query here

Expected Results: