Basic 3-Table Join - Order Details with Customer and Product Info

basic

Show order details with customer names and product information

-- Join orders with customers and products

SELECT

o.order_id,

c.customer_name,

p.product_name,

o.quantity,

o.unit_price,

(o.quantity * o.unit_price) as line_total

FROM orders o

INNER JOIN customers c ON o.customer_id = c.customer_id

INNER JOIN products p ON o.product_id = p.product_id

WHERE o.order_date >= '2024-01-01'

ORDER BY o.order_date DESC, o.order_id;

Shows order details with meaningful customer and product names instead of IDs

Two INNER JOINs chain together: orders→customers, then result→products. Each join adds columns from another table.

All

Mixed JOIN Types - Complete Customer Order Report

intermediate

Show all customers with their orders and product details, including customers who never ordered

-- Mix LEFT and INNER joins appropriately

SELECT

c.customer_name,

c.email,

o.order_id,

o.order_date,

p.product_name,

oi.quantity,

oi.unit_price

FROM customers c

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

LEFT JOIN order_items oi ON o.order_id = oi.order_id

INNER JOIN products p ON oi.product_id = p.product_id

WHERE c.status = 'active'

ORDER BY c.customer_name, o.order_date DESC;

Shows all active customers, their orders (if any), and product details for existing orders

LEFT JOINs preserve customers without orders. INNER JOIN to products only shows rows where order_items exist.

All