A join combines rows from two tables based on a relationship between them — usually a foreign key match. The seed has three tables: users, orders (with a user_id and a category_id), and categories. We'll wire them together.
INNER JOIN: rows on both sides match
The default join. Returns one row per matching pair from the two tables; rows that don't match on either side are dropped.
SELECT u.full_name, o.product, o.amount
FROM orders o
INNER JOIN users u ON u.id = o.user_id
ORDER BY o.amount DESC;
Two things going on:
- Table aliases (
o,u) — short prefixes save typing once you reference the same column from multiple tables. ON u.id = o.user_id— the join condition. Almost always foreign-key equality, but it can be any boolean expression.
Notice the result has 15 rows — exactly one per order, even though some users have multiple orders. The join expands the orders side, attaching the matching user to each.
Drop the INNER keyword
JOIN on its own means INNER JOIN. Most code in the wild omits the INNER.
SELECT u.full_name, o.product
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 5;
USING when the column names match
When both sides use the same column name (here, the id/user_id mismatch wouldn't qualify, but it does for the categories join), USING (col) is shorter than ON a.col = b.col. It also collapses the two columns into one in the result.
SELECT product, name AS category, amount
FROM orders
JOIN categories ON categories.id = orders.category_id
LIMIT 5;
That's the form. If the foreign-key column were named on both sides (it's not here — vs ), the form would be . In practice you'll see more often because matching column names are rarer than you'd think.