SQL Data Types
Handling NULL Values with IS NULL
NULL represents missing or unknown data and requires special handling in SQL.
- IS NULL β checks if a value is NULL
- IS NOT NULL β checks if a value exists Handle missing data gracefully in your queries.
What is NULL?
-- Find all customers without an email address
SELECT first_name, last_name
FROM customers
WHERE email IS NULL;
-- Find employees who haven't been assigned a department
SELECT employee_name
FROM employees
WHERE department_id IS NULL;
-- Find orders without a ship date
SELECT order_id, order_date
FROM orders
WHERE ship_date IS NULL;
NULL Comparison Rules
| Expression | Result | Explanation |
|---|---|---|
| NULL = NULL | UNKNOWN | Two NULLs are not equal |
| NULL != NULL | UNKNOWN | NULLs are not unequal either |
| NULL = 5 | UNKNOWN | NULL compared to any value |
| NULL > 0 | UNKNOWN | NULL in any comparison |
| NULL AND TRUE | UNKNOWN | Logical operations with NULL |
| NULL OR TRUE | TRUE | OR with TRUE yields TRUE |
| NOT NULL | UNKNOWN | NOT NULL remains UNKNOWN |
-- WRONG: This query returns nothing
SELECT * FROM users WHERE deleted_at = NULL;
-- CORRECT: Use IS NULL
SELECT * FROM users WHERE deleted_at IS NULL;
-- Find records where optional field has value
SELECT customer_id, phone_number
FROM customers
WHERE phone_number IS NOT NULL;
Common NULL Handling Patterns
-- Replace NULL with a default value
SELECT
first_name,
COALESCE(email, 'No email provided') AS email_display
FROM customers;
-- Use NULLIF to avoid division by zero
SELECT
product_name,
total_sales / NULLIF(units_sold, 0) AS avg_price
FROM sales_report;
-- COUNT only non-NULL values
SELECT COUNT(email) AS email_count
FROM customers;
-- COUNT all rows regardless of NULLs
SELECT COUNT(*) AS total_rows
FROM customers;
NULL Handling Functions
| Function | Purpose | Example | Result |
|---|---|---|---|
| COALESCE | First non-NULL value | COALESCE(a, b, c) | Returns first non-NULL |
| NULLIF | Returns NULL if equal | NULLIF(a, b) | NULL if a = b |
| ISNULL | Replace NULL (SQL Server) | ISNULL(a, 0) | 0 if a is NULL |
| IFNULL | Replace NULL (MySQL) | IFNULL(a, 0) | 0 if a is NULL |
| NVL | Replace NULL (Oracle) | NVL(a, 0) | 0 if a is NULL |
-- Find orders where optional discount was applied
SELECT order_id, discount_amount
FROM orders
WHERE discount_amount IS NOT NULL;
-- Identify incomplete customer records
SELECT customer_id
FROM customers
WHERE email IS NULL
OR phone_number IS NULL
OR address IS NULL;
-- Aggregate with NULL handling
SELECT
department,
AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM employees
GROUP BY department;