<aside> ❓ NULL represents missing or unknown data. Special functions are required to handle NULLs correctly.

</aside>

1. COALESCE() — Replace NULL with a Value

Returns the first non-NULL value in a list.

-- Replace NULL score with 0
SELECT
    CustomerID, Score,
    COALESCE(Score, 0) AS CleanScore
FROM Sales.Customers;

-- Build full name safely (handles NULL LastName)
SELECT
    FirstName + ' ' + COALESCE(LastName, '') AS FullName
FROM Sales.Customers;

2. NULLIF() — Return NULL if Two Values Are Equal

NULLIF(a, b) returns NULL if a = b, otherwise returns a. Useful to avoid division by zero.

-- Avoid division by zero
SELECT
    OrderID, Sales, Quantity,
    Sales / NULLIF(Quantity, 0) AS Price
FROM Sales.Orders;

3. IS NULL / IS NOT NULL

-- Find customers with no score
SELECT * FROM Sales.Customers WHERE Score IS NULL;

-- Find customers who have a score
SELECT * FROM Sales.Customers WHERE Score IS NOT NULL;

<aside> ⚠️ Never use = NULL. Use IS NULL instead.

</aside>

4. Sorting NULLs Last

SELECT CustomerID, Score
FROM Sales.Customers
ORDER BY CASE WHEN Score IS NULL THEN 1 ELSE 0 END, Score;

5. CASE Statement

CASE evaluates conditions and returns different values based on the result.

Searched CASE (most flexible)