JOINs with NULL

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.

Exercise JOIN with NULL

Use COALESCE to show 0 instead of NULL in a LEFT JOIN result.

Show example solution

SELECT c.name, COALESCE(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Test yourself

What provides a value instead of NULL?
- COALESCE
- COUNT
- GROUP BY
- DISTINCT
Show answer

COALESCE — COALESCE puts a default instead of NULL.

Add COALESCE

SELECT c.name, ____(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Show answer

SELECT c.name, COALESCE(o.id, 0) AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

JOINs with NULL: match the terms

COALESCE

default
NULL

empty
LEFT JOIN

all left
AS

alias

Next lesson: LEFT vs RIGHT

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies