Basic LEFT JOIN - All Customers Including Those Without Orders

basic

Marketing dashboard needs complete customer list with order information. Must include customers who never ordered.

SELECT

c.id,

c.name,

c.email,

o.id AS order_id,

o.order_date,

o.total

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

ORDER BY c.name;

customers:

id | name | email

1 | Alice Brown | alice@email.com

2 | Bob Wilson | bob@email.com

3 | Carol Davis | carol@email.com

orders:

id | customer_id | order_date | total

101 | 1 | 2024-01-15 | 299.99

102 | 1 | 2024-01-20 | 149.50

103 | 3 | 2024-01-18 | 599.99

id | name | email | order_id | order_date | total

1 | Alice Brown | alice@email.com | 101 | 2024-01-15 | 299.99

1 | Alice Brown | alice@email.com | 102 | 2024-01-20 | 149.50

2 | Bob Wilson | bob@email.com | NULL | NULL | NULL

3 | Carol Davis | carol@email.com | 103 | 2024-01-18 | 599.99

Note: Bob Wilson appears with NULL order columns (no orders)

LEFT JOIN preserves ALL customers from left table. Alice has 2 orders (appears twice). Bob has no orders (appears once with NULL order columns). Carol has 1 order. Compare to INNER JOIN which would exclude Bob entirely.

All

Finding Customers Without Orders (Anti-Join Pattern)

intermediate

Marketing team needs list of customers who never placed orders for targeted promotion campaign.

SELECT

c.id,

c.name,

c.email,

c.signup_date

FROM customers c