What is NULL in SQL?
NULL means missing or unknown data.
It does NOT mean:
- 0
- Empty string ''
- False
👉 NULL = no value at all
Why NULLs exist in real data
Real business data is messy:
- Customer didn’t provide city
- Order amount not updated yet
- Employee not assigned a manager
So databases allow NULLs.
Example Table
Data: customers
customer_id: 1, name: Rahul, city: Pune, email: rahul@gmail.com
customer_id: 2, name: Neha, city: NULL, email: neha@gmail.com
customer_id: 3, name: Aman, city: Delhi, email: NULL
🚫 Biggest Beginner Mistake
❌ This is WRONG
SELECT * FROM customers WHERE city = NULL;
👉 This will return no rows
Why? Because NULL cannot be compared using = or !=
✅ Correct Way to Handle NULL
1️⃣ IS NULL: Used to find missing values
SELECT * FROM customers WHERE city IS NULL;
What this query does:
- Scans all rows
- Returns customers where city is missing
2️⃣ IS NOT NULL: Used to exclude missing values
SELECT * FROM customers WHERE email IS NOT NULL;
What this query does:
- Returns customers who have email
- Removes rows with NULL email
NULLs in JOINs (Very Important)
Customers data:
customer_id: 1, name: Rahul
customer_id: 2, name: Neha
customer_id: 3, name: Aman
Orders data:
order_id: 101, customer_id: 1, amount: 5000
order_id: 102, customer_id: 1, amount: 3000
LEFT JOIN with NULL check
👉 Find customers with NO orders
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
What this query does:
- Keeps all customers
- Matches orders where possible
- Filters customers without orders
NULLs in Aggregations
COUNT behavior (INTERVIEW FAVORITE)
SELECT COUNT(*) FROM customers;
👉 Counts all rows
SELECT COUNT(city) FROM customers;
👉 Counts only non-NULL cities
Example
SELECT COUNT(email) FROM customers;
✔ Counts customers who have email
❌ Ignores NULL emails
Handling NULL using COALESCE
SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers;
What this query does:
- If city exists → show city
- If city is NULL → show “Unknown”
NULLs in SUM / AVG
SELECT SUM(amount) FROM orders;
👉 NULL values are ignored, not treated as 0
But if all rows are NULL, result is NULL.
Safe approach:
SELECT COALESCE(SUM(amount), 0) FROM orders;
NULL vs Empty String
- NULL: No value
- '': Empty value
WHERE email IS NULL -- missing
WHERE email = '' -- empty but exists
Common NULL Mistakes (Must Avoid)
❌ Using = NULL
❌ Forgetting NULLs in LEFT JOIN
❌ Assuming COUNT(column) counts NULL
❌ Ignoring NULL replacement in reports
Interview One-Liner 💡
> NULL represents missing data and must be handled using IS NULL, IS NOT NULL, or COALESCE, not with =.
Double Tap ♥️ For More