JOINs with NULL
- SQL course
- Intermediate
- Lesson 10/115
- 4 min
See NULL on the unmatched side.
JOIN with NULL
In a LEFT JOIN unmatched right columns become NULL. COALESCE gives them a default value.
SELECT c.name, COALESCE(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;This query writes 0 for those without orders.
c.name, COALESCE(o.id, 0) AS order_id is selected from customers c. orders o is joined on o.customer_id = c.id, keeping every row of the left table.
JOIN with NULL — details
NULL values break reports. COALESCE replaces NULL with a meaningful value.
For example, COALESCE(SUM(o.amount), 0) shows a total of 0 instead of empty for a customer with no orders.
Practice JOIN with NULL
Use COALESCE to show 0 instead of NULL in a LEFT JOIN result.
Starter code
-- zero instead of null
SELECT c.name,Sample solution
SELECT c.name, COALESCE(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;Expected output
name order_id Ayşe 1 Fatma 0
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What provides a value instead of NULL?
- COALESCE Correct answer
- COUNT
- GROUP BY
- DISTINCT
Explanation: COALESCE puts a default instead of NULL.
2 Add COALESCE
SELECT c.name, ____(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;SELECT c.name, COALESCE(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;Explanation: COALESCE is the right answer.
3 JOINs with NULL: match the terms
| Term | Meaning |
|---|---|
COALESCE |
default |
NULL |
empty |
LEFT JOIN |
all left |
AS |
alias |