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.

Database Preparation

Run this block to set up the Products table.

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);

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:**
```sql
SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

2. ISNULL() (SQL Server)

Query (SQL Server Syntax):

-- Note: SQLite does not support ISNULL, using COALESCE for this example
SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

3. IFNULL() (MySQL)

Query (MySQL/SQLite Syntax):

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

4. NVL() (Oracle)

Query (Oracle Syntax):

-- Note: SQLite does not support NVL, using COALESCE for this example
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:

You can also chain multiple values. For example, COALESCE(column1, column2, 'default') would return column1 if it's not null, otherwise column2 if it's not null, otherwise 'default'.

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.

Operation: (Same as above) Display all products, substituting NULL discounts with 0.00.

Query (SQL Server Syntax):

-- Note: SQLite does not support ISNULL, using COALESCE for this example
SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

Result: This produces the exact same result as the COALESCE() example.

3. IFNULL() (MySQL)

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.

Operation: (Same as above)

Query (MySQL/SQLite Syntax):

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

Result: This also produces the exact same result as the COALESCE() example.

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.

Operation: (Same as above)

Query (Oracle Syntax):

-- Note: SQLite does not support NVL, using COALESCE for this example
SELECT ProductName, Price, COALESCE(Discount, 0.00) AS EffectiveDiscount
FROM Products;

Result: This also produces the exact same result as the COALESCE() example.

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. You should only use the others if you are specifically writing code for a database system that requires them.

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: