What is NULL in SQL?

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It signifies missing or unknown information, and it's crucial to understand its unique behavior as it differs significantly from an empty string, zero, or any other actual value.

What is NULL?

NULL represents the absence of a value. It's not a value itself but rather a placeholder for data that is unknown, not applicable, or simply missing. This distinction is fundamental because it affects how NULL interacts with operators, functions, and comparisons in SQL queries.

NULL Comparisons

One of the most common pitfalls with NULL is its behavior in comparisons. A direct comparison using standard equality operators (=, <>, <, >, <=, >=) with NULL will always result in UNKNOWN, not TRUE or FALSE. This is because you cannot compare an unknown value to another value, not even another unknown value.

To correctly check for NULL values, you must use the special IS NULL or IS NOT NULL predicates.

SELECT (NULL = NULL); -- Result: NULL (or UNKNOWN, depending on SQL dialect)
SELECT (NULL IS NULL); -- Result: TRUE
SELECT (5 = NULL);    -- Result: NULL
SELECT (5 IS NULL);   -- Result: FALSE

Impact on Aggregate Functions

Most aggregate functions (like SUM(), AVG(), MAX(), MIN()) ignore NULL values. COUNT(*) counts all rows, including those with NULLs, but COUNT(column_name) counts only non-NULL values in that specific column. This behavior can significantly affect statistical calculations.

-- Sample Data:
-- ID | Value
-- -- | -----
-- 1  | 10
-- 2  | 20
-- 3  | NULL
-- 4  | 30

SELECT SUM(Value) FROM MyTable; -- Result: 60 (10 + 20 + 30)
SELECT AVG(Value) FROM MyTable; -- Result: 20 (60 / 3)
SELECT COUNT(*) FROM MyTable;   -- Result: 4
SELECT COUNT(Value) FROM MyTable; -- Result: 3

Handling NULL Values

SQL provides several functions to handle or convert NULL values, allowing for more robust query results.

IS NULL / IS NOT NULL

As mentioned, these are the standard predicates for checking the presence or absence of NULL.

SELECT Name, Age
FROM Employees
WHERE Age IS NULL; -- Finds employees with unknown age

SELECT ProductName
FROM Products
WHERE Description IS NOT NULL; -- Finds products with a description

COALESCE / IFNULL / NVL

These functions (syntax varies by database system) allow you to return the first non-NULL expression in a list. They are useful for replacing NULLs with a default value for display or calculation purposes.

• COALESCE(expression1, expression2, ...) (Standard SQL)
• IFNULL(expression1, expression2) (MySQL, SQLite)
• NVL(expression1, expression2) (Oracle)

SELECT ProductName, COALESCE(Price, 0) AS ActualPrice
FROM Products; -- If Price is NULL, it will be displayed as 0

SELECT EmployeeName, IFNULL(Commission, 'N/A') AS CommissionStatus
FROM Sales; -- If Commission is NULL, it will be 'N/A'

Key Takeaways

• NULL means 'unknown' or 'missing data', not zero or an empty string.
• Comparisons with NULL using =, <>, etc., always result in UNKNOWN.
• Use IS NULL or IS NOT NULL to check for NULLs.
• Aggregate functions like SUM() and AVG() ignore NULLs, while COUNT(column) also ignores them but COUNT(*) does not.
• Functions like COALESCE, IFNULL, and NVL are used to provide default values when a NULL is encountered.