SQL - Null Functions

SQL - Handling NULL Values

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is not the same as an empty string '' or the number 0. Understanding how to correctly handle NULL is essential for writing accurate and bug-free queries.

1. The Concept of NULL and Three-Valued Logic

Think of NULL as ‘unknown’ or ‘not applicable’. Because it represents an unknown, NULL behaves differently in comparisons:

• A comparison between NULL and any other value (including another NULL) results in UNKNOWN, not TRUE or FALSE.
• This is why column = NULL or column != NULL will not work as expected. The WHERE clause only includes rows that evaluate to TRUE.
• This system is known as three-valued logic (TRUE, FALSE, UNKNOWN).

Example Data

Let’s use a CUSTOMERS table where some SALARY and ADDRESS values are missing (NULL).

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2)
);

INSERT INTO CUSTOMERS VALUES
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, NULL, NULL),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Komal', 22, 'Hyderabad', NULL);

2. Checking for NULL: IS NULL and IS NOT NULL

The correct way to filter rows based on NULL is to use the IS NULL and IS NOT NULL operators.

Example: Find customers with no salary information.

SELECT NAME, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

Output:

NAME	SALARY
Kaushik	NULL
Komal	NULL

3. Replacing NULL with a Default Value: COALESCE

The COALESCE function is the standard, most versatile way to substitute NULL values. It accepts a list of arguments and returns the first non-NULL value it encounters.

Syntax

COALESCE(value1, value2, ..., valueN)

Example: Display ‘Not Provided’ for NULL salaries in a report.

Note that we must cast the replacement value to match the column’s data type.

SELECT
    NAME,
    SALARY,
    COALESCE(CAST(SALARY AS VARCHAR(20)), 'Not Provided') AS DisplaySalary
FROM CUSTOMERS;

Example: Use a backup column.

Imagine you have a CONTACT_NUMBER and a WORK_NUMBER. You want to display the contact number, but if it’s NULL, use the work number.

SELECT NAME, COALESCE(CONTACT_NUMBER, WORK_NUMBER, 'No number available') AS PrimaryContact FROM USERS;

4. Platform-Specific Alternatives

While COALESCE is standard, many RDBMS have their own functions. It’s good to recognize them, but COALESCE is generally preferred for portability.

• ISNULL(expression, replacement_value) (SQL Server): Similar to a two-argument COALESCE. Be aware of data type precedence rules, which can differ from COALESCE.
• IFNULL(expression, replacement_value) (MySQL): Functionally identical to SQL Server’s ISNULL.
• NVL(expression, replacement_value) (Oracle): Oracle’s equivalent function.

5. Comparing Expressions with NULLIF

The NULLIF function takes two arguments and returns NULL if they are equal. Otherwise, it returns the first argument. This is useful for data cleaning, like converting a specific placeholder string to a proper NULL.

Syntax

NULLIF(expression1, expression2)

Example: Treat ‘N/A’ as NULL.

If a column stores the string ‘N/A’ to represent missing data, you can convert it to a true NULL for calculations.

-- Imagine a column 'product_weight' that is VARCHAR and sometimes contains 'N/A'.
SELECT product_name, NULLIF(product_weight, 'N/A') AS CleanedWeight FROM Products;

6. Common Pitfalls with NULL

• Using = or != with NULL: The most common mistake. Always use IS NULL or IS NOT NULL.
• Aggregate Functions: Functions like SUM(), AVG(), MAX(), etc., ignore NULL values. This is usually the desired behavior, but you must be aware of it. For example, AVG(SALARY) is the average of non-NULL salaries, not the total salary divided by the total number of customers.
• String Concatenation: In many RDBMS (like SQL Server), concatenating a string with NULL results in NULL. For example, FirstName + ' ' + NULL yields NULL. Use COALESCE to handle this: FirstName + ' ' + COALESCE(LastName, '').