INNER JOIN: Stitching Tables Together

~14 min · queries, joins

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The matchmaking dance

An INNER JOIN combines two tables on a shared key. Only rows that have a match in both tables survive. If a user has no orders, they don't appear. If an order has no matching user (a foreign-key orphan), it doesn't appear either.

The ON clause

The ON clause defines the match — usually foreign_key = primary_key. Multiple conditions are fine: ON o.user_id = u.id AND o.status = 'paid'. Filters that mention the right-hand table belong in ON if you want LEFT JOIN semantics later — see the LEFT JOIN lesson.

Multi-table joins

Chain JOINs to traverse relationships: user → order → line_item → product. PostgreSQL's planner is smart about reordering joins for performance — your job is to write the joins clearly.

Code

Two-table INNER JOIN·sql

SELECT u.name, o.id AS order_id, o.total, o.placed_at
FROM   users u
INNER JOIN orders o ON o.user_id = u.id
WHERE  o.placed_at >= now() - INTERVAL '30 days'
ORDER  BY o.placed_at DESC;

Multi-table chain·sql

SELECT u.name, o.id AS order_id, p.title, li.quantity, li.unit_price
FROM   users u
INNER JOIN orders     o  ON o.user_id   = u.id
INNER JOIN line_items li ON li.order_id = o.id
INNER JOIN products   p  ON p.id        = li.product_id
ORDER  BY o.placed_at DESC, li.id;

INNER JOIN can be written with USING·sql

-- USING (col) requires the column to have the same name in both tables
-- and avoids the ambiguity of WHICH table's column appears in SELECT.
SELECT u.name, o.id AS order_id
FROM   users u
INNER JOIN orders o USING (user_id);  -- only works if both have a user_id

External links

PostgreSQL JOIN syntax

Exercise

Write a 4-table JOIN: users → orders → order_items → products. Filter to last month, sort by total descending, return user name + product name + line total.