Relationships and Joins

How tables relate

The relational model's power comes from splitting data into focused tables and reconnecting them with joins. Modeling the relationships correctly is the heart of schema design.

Cardinality: the three relationship types

• One-to-one — each row in A maps to at most one row in B (e.g., users ↔ user_profiles). Put a unique foreign key on one side.
• One-to-many — one row in A maps to many in B (e.g., one customer has many orders). The "many" side holds a foreign key to the "one" side.
• Many-to-many — rows in A map to many in B and vice versa (e.g., students ↔ courses). Requires a junction table (also called a join or associative table).

The junction table

A many-to-many relationship is modeled by a third table whose rows each pair one A with one B:

-- students ↔ courses, many-to-many
students(student_id PK, name)
courses(course_id PK, title)

-- junction table: each row = one enrollment
enrollments(
  student_id FK → students,
  course_id   FK → courses,
  enrolled_at,
  PRIMARY KEY (student_id, course_id)
)

The junction's primary key is usually the composite of both foreign keys, which prevents duplicate pairs.

Foreign keys

A foreign key is a column (or set of columns) whose values must match a primary key in another table. The constraint enforces referential integrity: you cannot insert an order pointing at a customer that doesn't exist, and (depending on ON DELETE rules) you cannot delete a customer still referenced by orders.

CREATE TABLE orders (
  order_id   SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
                ON DELETE CASCADE   -- delete orders when customer deleted
);

INNER JOIN vs LEFT JOIN

-- INNER JOIN: only rows that match in BOTH tables
SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- LEFT JOIN: every row from the LEFT table; NULLs where no match
SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

• INNER JOIN drops rows without a match — customers with zero orders vanish.
• LEFT JOIN keeps every left row, filling NULLs — useful for "customers and their orders, including customers with none."

Pick the join by which rows you want to keep. INNER JOIN answers "matching pairs." LEFT JOIN answers "everything from the left, matched where possible." RIGHT and FULL joins exist but are used far less often.