Relationships and Joins

Real apps don't cram everything into one giant table. A blog has users, posts and comments. An online store has customers, orders and products. Those tables are related to each other, and once relationships click for you, you'll see why relational databases are such a big deal.

Why Split Data Into Multiple Tables?#

Picture stuffing everything into one table.

-- BAD: Everything in one table (lots of repeated data!)
-- order_id | customer_name | customer_email     | product_name | product_price | quantity
-- 1        | Alice         | [email protected]  | Laptop       | 999.99        | 1
-- 2        | Alice         | [email protected]  | Mouse        | 29.99         | 2
-- 3        | Bob           | [email protected]    | Laptop       | 999.99        | 1

The problems jump right out at you.

• Alice's name and email get repeated on every order she places
• The laptop's price lives in more than one spot
• If Alice changes her email, you're stuck updating every single row

The fix is called normalization, which just means splitting data into separate tables and connecting them with relationships.

Foreign Keys: The Link Between Tables#

A foreign key is a column in one table that points at the primary key of another table. It's the glue holding related data together.

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
 
-- Orders table (has a foreign key pointing to users)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total DECIMAL NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

The user_id column in orders is a foreign key. It stores the id of whoever placed that order. The REFERENCES users(id) constraint makes sure you can't create an order for a user who doesn't exist.

Types of Relationships#

One-to-Many (Most Common)#

One record in table A can relate to lots of records in table B, but every record in B belongs to just one record in A.

-- One user can have MANY posts
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL
);

Think one user with many orders, one author with many books or one category with many products.

One-to-One#

Each record in table A relates to exactly one record in table B.

-- Each user has exactly one profile
CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
  bio TEXT,
  avatar_url TEXT
);

The UNIQUE constraint on user_id makes sure each user only ever gets one profile.

Many-to-Many#

Records on both sides can relate to multiple records on the other side. For this one you need a junction table (also called a join table).

-- Students and Courses: many-to-many
CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);
 
CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);
 
-- Junction table connecting students and courses
CREATE TABLE enrollments (
  id SERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(student_id, course_id)  -- prevent duplicate enrollments
);

One student can sign up for lots of courses, and one course can have lots of students.

Joining Tables: Combining Related Data#

So your data's split across tables now. How do you put it back together? That's the whole job of JOINs. They combine rows from two or more tables based on a column they share.

INNER JOIN#

Gives you back only the rows that have a match in both tables.

-- Get all orders with the user's name
SELECT users.name, orders.total, orders.created_at
FROM orders
INNER JOIN users ON orders.user_id = users.id;
 
-- Result:
-- name  | total  | created_at
-- Alice | 149.99 | 2024-01-15
-- Alice | 29.99  | 2024-01-20
-- Bob   | 599.99 | 2024-01-18

The ON clause tells the database how the tables connect. Users with zero orders won't show up, and neither will orders without a valid user.

LEFT JOIN#

Gives you back all the rows from the left table, plus any matching rows from the right table. When there's no match, the right side just shows NULL.

-- Get all users, even those who haven't placed orders
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
 
-- Result:
-- name    | total
-- Alice   | 149.99
-- Alice   | 29.99
-- Bob     | 599.99
-- Charlie | NULL      <-- Charlie has no orders

RIGHT JOIN and FULL JOIN#

• RIGHT JOIN is the mirror image of LEFT JOIN. All rows from the right table, plus matches from the left.
• FULL JOIN gives you all rows from both tables, with NULL wherever there's no match.

In real life, LEFT JOIN covers almost everything. You can always just flip the table order instead of reaching for RIGHT JOIN.

Using Table Aliases#

Once queries get long, table aliases keep them readable.

-- Use short aliases (u for users, o for orders)
SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100
ORDER BY o.created_at DESC;

Joining Multiple Tables#

You can join as many tables as you need.

-- Get order details: who bought what
SELECT
  u.name AS customer,
  p.name AS product,
  oi.quantity,
  oi.quantity * p.price AS line_total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.id = 42;

That one query ties FOUR tables together to paint the full picture of a single order.

Finding Missing Relationships#

LEFT JOIN is perfect for finding records without any related data.

-- Find users who have never placed an order
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
 
-- Find products that have never been ordered
SELECT p.name, p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

Here's the trick. LEFT JOIN pulls in all the rows, and then WHERE right_table.id IS NULL keeps only the ones that didn't match.

GROUP BY with JOINs#

Pair JOINs with GROUP BY and you get some seriously useful summaries.

-- Count how many orders each user has placed
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.id, u.name  -- group by id so two users named Alice stay separate
ORDER BY order_count DESC;
 
-- Total spending per user
SELECT u.name, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name  -- group by id so two users named Alice stay separate
ORDER BY total_spent DESC;

TL;DR#

• Foreign keys connect tables by pointing at the primary key of another table
• One-to-many is the most common relationship (one user has many orders)
• Many-to-many relationships need a junction table in the middle
• INNER JOIN returns only rows with matches in both tables
• LEFT JOIN returns every row from the left table, with NULLs where the right side has no match
• Use LEFT JOIN with WHERE right.id IS NULL to find records with no related data
• Table aliases (u, o, p) keep big queries readable
• GROUP BY with JOINs gives you summaries across tables

What's Next?#

Raw SQL works great. In a real app though, you'll want something that plugs right into your TypeScript code. Next up is Prisma, an ORM that lets you work with your database using type-safe TypeScript instead of raw SQL strings...