Introduction to JOIN

Order only stores a user_id, not the user's name or email. That keeps user data in one place.

JOIN reunites split tables by matching a foreign key back to its row.

Order rows alone

Order only carries user_id — no name, no email.

Loaded Database:introduction-to-join.database-init

SELECT * FROM "Order";

id	user_id	amount
1	1	42.00
2	1	15.50
3	2	9.99

Seq Scan on "Order"  (cost=0.00..1.03 rows=3 width=14)

Combine with JOIN

JOIN matches Order.user_id to User.id, pulling both tables into one result.

Loaded Database:introduction-to-join.database-init

SELECT "Order".id, "User".name, "Order".amount
FROM "Order"
JOIN "User" ON "User".id = "Order".user_id
ORDER BY "Order".id;

id	name	amount
1	Ada Lovelace	42.00
2	Ada Lovelace	15.50
3	Grace Hopper	9.99

Sort  (cost=2.15..2.16 rows=3 width=23)
  Sort Key: "Order".id
  ->  Nested Loop  (cost=0.00..2.12 rows=3 width=23)
        Join Filter: ("User".id = "Order".user_id)
        ->  Seq Scan on "Order"  (cost=0.00..1.03 rows=3 width=14)
        ->  Materialize  (cost=0.00..1.03 rows=2 width=17)
              ->  Seq Scan on "User"  (cost=0.00..1.02 rows=2 width=17)

What We Learned

JOINcombines rows from two relations by matching column values.
Foreign keya column referencing another table's primary key.
ON clausetells PostgreSQL which columns from each side must match.