SQL Tutorial: NULL Functions

In SQL Server, NULL represents the absence of a value. Working with NULLs can be challenging because NULL values propagate through expressions and comparisons in unexpected ways. SQL Server provides several functions specifically designed to handle NULL values.

Understanding NULL
NULL is not equivalent to an empty string or zero; it is a distinct marker for missing or unknown data.
Any comparison with NULL returns NULL, not true or false. For example, NULL = NULL yields NULL, not true.
IS NULL and IS NOT NULL
Use IS NULL and IS NOT NULL to check for NULL values in a column.

Example:
SELECT ProductID, ProductName, Price
FROM Products
WHERE Price IS NULL;

SELECT ProductID, ProductName, Price
FROM Products
WHERE Price IS NOT NULL;

COALESCE Function
The COALESCE function returns the first non-NULL value from a list of expressions. It is often used to provide a default value.

Syntax:
COALESCE(expression1, expression2, …, expressionN)

Example:
SELECT ProductID, ProductName,
COALESCE(Price, 0) AS Price
FROM Products;

In this example, if Price is NULL, it will return 0.

ISNULL Function
The ISNULL function replaces NULL with a specified replacement value. It is similar to COALESCE but limited to two arguments.

Syntax:
ISNULL(expression, replacement_value)

#Example
SELECT ProductID, ProductName,
ISNULL(Price, 0) AS Price
FROM Products;

In this example, if Price is NULL, it will return 0.

NULLIF Function
The NULLIF function returns NULL if the two specified expressions are equal; otherwise, it returns the first expression.

Syntax:
NULLIF(expression1, expression2)
# Example
SELECT ProductID, ProductName,
NULLIF(Price, 0) AS Price
FROM Products;

In this example, if Price is 0, it will return NULL; otherwise, it returns the Price.

CASE Statement
The CASE statement can be used to handle NULL values by explicitly defining conditions.

Syntax:
CASE
WHEN expression IS NULL THEN result
ELSE expression
END
# Example
SELECT ProductID, ProductName,
CASE
WHEN Price IS NULL THEN ‘No Price’
ELSE CAST(Price AS VARCHAR)
END AS Price
FROM Products;

In this example, if Price is NULL, it will return ‘No Price’; otherwise, it returns the Price.

Handling NULLs in Aggregate Functions
Aggregate functions like SUM, AVG, COUNT, MAX, and MIN ignore NULL values, except COUNT(*).

Examples:
SUM and AVG: Ignore NULL values.
SELECT SUM(Price) AS TotalPrice,
AVG(Price) AS AveragePrice
FROM Products;
#COUNT:

COUNT(column_name) ignores NULLs, while COUNT(*) includes NULLs.
SELECT COUNT(Price) AS NonNullPriceCount,
COUNT(*) AS TotalCount
FROM Products;

Using NULL Functions in Practice
Consider a table Orders with columns OrderID, CustomerID, OrderDate, and Discount.

Example Table Data:

Example Queries:
Replace NULL in Discount with 0:
SELECT OrderID, CustomerID, OrderDate,
ISNULL(Discount, 0) AS Discount
FROM Orders;

# Provide default date if OrderDate is NULL:
SELECT OrderID, CustomerID,
COALESCE(OrderDate, ‘1900-01-01’) AS OrderDate
FROM Orders;
# Check for specific NULL conditions using CASE:
SELECT OrderID, CustomerID,
CASE
WHEN OrderDate IS NULL THEN ‘No Date’
ELSE CAST(OrderDate AS VARCHAR)
END AS OrderDate
FROM Orders;

Handling NULL values effectively is crucial for accurate data querying and reporting in SQL Server. Understanding and using NULL functions like ISNULL, COALESCE, NULLIF, and conditional expressions can help you manage NULL values efficiently.