Databases Lesson 3: Joins

Joins combine data from multiple tables. They’re what make relational databases powerful — data is normalized into separate tables and joined at query time.

Types of Joins

-- Setup:
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  product VARCHAR(100),
  total DECIMAL(10,2),
  status VARCHAR(20)
);

-- INNER JOIN: only rows that match in BOTH tables
SELECT u.name, o.product, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: all from left + matching from right (nulls if no match)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- Right JOIN, FULL OUTER JOIN also exist

Multi-Table Join

SELECT
  u.name as customer,
  p.name as product,
  oi.quantity,
  oi.quantity * p.price as subtotal
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY subtotal DESC;