How do you join 3 or more tables in SQL?

Chain multiple JOIN clauses together. Each join needs its own ON clause. Joins execute left-to-right: first join combines tables 1 and 2, then the result joins with table 3, etc. Use table aliases for readability and mix INNER/LEFT joins based on requirements.

What happens when you mix INNER JOIN and LEFT JOIN in a multi-table query?

The join types affect which rows are preserved. LEFT JOIN preserves left table rows, but if followed by INNER JOIN, the INNER JOIN can eliminate those NULL rows. Order matters: LEFT JOIN then INNER JOIN behaves differently than INNER JOIN then LEFT JOIN.

How do you optimize performance for multi-table joins?

Index ALL join columns (foreign keys and primary keys). Filter early with WHERE clause. Select only needed columns, not SELECT *. Join smaller tables first when possible. Use EXPLAIN to check execution plans. Consider denormalization for frequently joined tables.

Write a query to show customer names, order dates, and product names for all orders in the last 30 days.

SELECT c.customer_name, o.order_date, p.product_name

FROM customers c

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

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

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

WHERE o.order_date >= CURRENT_DATE - INTERVAL 30 DAY

ORDER BY o.order_date DESC;

Three INNER JOINs chain customers→orders→order_items→products. WHERE filters for recent orders.

Create a query showing all customers with their total order count and total spent, including customers who never ordered.

SELECT c.customer_name,

COUNT(o.order_id) as order_count,

COALESCE(SUM(oi.quantity * oi.unit_price), 0) as total_spent

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

GROUP BY c.customer_id, c.customer_name

ORDER BY total_spent DESC;

LEFT JOINs preserve all customers. COUNT and SUM with COALESCE handle customers without orders.

Write a query to find the top 5 product categories by revenue, showing category name and total sales.

SELECT cat.category_name,

SUM(oi.quantity * oi.unit_price) as total_revenue

FROM categories cat

INNER JOIN products p ON cat.category_id = p.category_id

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

INNER JOIN orders o ON oi.order_id = o.order_id

GROUP BY cat.category_id, cat.category_name

ORDER BY total_revenue DESC

LIMIT 5;

Four-table join: categories→products→order_items→orders. GROUP BY category, ORDER BY revenue, LIMIT for top 5.