Inner join and left join

JOINs are the most important skill in SQL. Real data lives in separate tables: customers in one, orders in another, products in a third. JOINs connect them. If you can JOIN, you can answer any question that spans multiple tables.

Inner join: only matching rows

inner_join.sql
sql
-- INNER JOIN: orders with customer info
SELECT
  o.order_id,
  o.order_status,
  c.customer_city,
  c.customer_state
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
LIMIT 10;

INNER JOIN returns only rows where the join condition matches in BOTH tables. Use table aliases (o, c) to keep queries readable.

Left join: all left + matches from right

left_join.sql
sql
-- LEFT JOIN: find products never sold (anti-join pattern)
SELECT
  p.product_id,
  p.product_category_name,
  oi.order_id
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

-- These are products that exist but have never been ordered

LEFT JOIN keeps ALL rows from the left table. When there is no match on the right, those columns are NULL. Filtering WHERE right.col IS NULL gives you the anti-join pattern: find things with NO match.

LEFT JOIN promises to keep every row from the left table, even if there is no matching row on the right. When there is no match, SQL fills the right-side columns with NULL. This is actually useful: it lets you find "missing" relationships like products never sold or customers who never ordered.

Fill in the blanks: Complete the join

Loading practice…

Code playground: 10 most expensive orders

Loading practice…

Quiz: Quiz

Loading practice…

Hints: Hints

Loading practice…