PostgreSQL Zero to Advanced #6: Relationships & JOINs

In Part 5 our "top customers" report could only show user_id values, because names live in a different table. That is the whole point of a relational database: data is split into focused tables and recombined when needed. In this part we make the relationships official with foreign keys and learn every kind of JOIN.

Foreign keys: making relationships official

Right now orders.user_id is just an integer. Nothing stops us from inserting an order for user 99, who does not exist. A foreign key tells PostgreSQL that a column must reference an existing row in another table. Let's add them to the tables we created in Part 2:

ALTER TABLE orders
    ADD CONSTRAINT orders_user_id_fkey
        FOREIGN KEY (user_id) REFERENCES users (id),
    ADD CONSTRAINT orders_course_id_fkey
        FOREIGN KEY (course_id) REFERENCES courses (id);

ALTER TABLE courses
    ADD CONSTRAINT courses_prerequisite_id_fkey
        FOREIGN KEY (prerequisite_id) REFERENCES courses (id);

Output:

ALTER TABLE
ALTER TABLE

The second statement is a self-reference: courses.prerequisite_id points to another row in the same courses table. Now the database protects us:

INSERT INTO orders (user_id, course_id, amount)
VALUES (99, 1, 150000);

Output:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL:  Key (user_id)=(99) is not present in table "users".

It also protects the other side. We cannot delete Budi while his orders still point to him:

DELETE FROM users WHERE id = 1;

Output:

ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

That default behaviour is called NO ACTION (similar to RESTRICT). You can choose other behaviours when declaring the key:

Option	When the parent row is deleted...
ON DELETE NO ACTION / RESTRICT	Reject the delete (default)
ON DELETE CASCADE	Delete the child rows too
ON DELETE SET NULL	Set the foreign key column to NULL

For orders, rejecting is the right choice: you never want to lose purchase history silently.

Adding an instructors table

To make the JOIN examples more interesting, let's add instructors. A course may have one instructor; an instructor may teach many courses (a one-to-many relationship). This time we declare the foreign key inline and use ON DELETE SET NULL, so removing an instructor simply leaves their courses unassigned:

CREATE TABLE instructors (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

INSERT INTO instructors (name, email) VALUES
    ('Hendra Gunawan', 'hendra@example.com'),
    ('Lina Marlina',   'lina@example.com'),
    ('Joko Prasetyo',  'joko@example.com');

ALTER TABLE courses
    ADD COLUMN instructor_id INTEGER
        REFERENCES instructors (id) ON DELETE SET NULL;

UPDATE courses SET instructor_id = 1 WHERE id IN (1, 2, 5);
UPDATE courses SET instructor_id = 2 WHERE id IN (3, 4, 6);

Output:

CREATE TABLE
INSERT 0 3
ALTER TABLE
UPDATE 3
UPDATE 3

Joko has no courses yet, and Python and Docker have no instructor yet. Keep that in mind for the outer joins below.

INNER JOIN

INNER JOIN returns only rows that have a match on both sides. Here is every order with the customer's name and course title. Short table aliases (o, u, c) keep the query readable:

SELECT o.id     AS order_id,
       u.name   AS customer,
       c.title  AS course,
       o.amount,
       o.status
FROM orders o
INNER JOIN users   u ON u.id = o.user_id
INNER JOIN courses c ON c.id = o.course_id
ORDER BY o.id;

Output:

 order_id |   customer   |            course             | amount |  status  
----------+--------------+-------------------------------+--------+----------
        1 | Budi Santoso | SQL Fundamentals              | 150000 | paid
        2 | Budi Santoso | Advanced SQL Queries          | 275000 | paid
        3 | Siti Rahma   | JavaScript Basics             | 120000 | paid
        4 | Siti Rahma   | React for Beginners           | 250000 | paid
        5 | Andi Wijaya  | SQL Fundamentals              | 150000 | paid
        6 | Andi Wijaya  | Python for Data Analysis      | 300000 | pending
        7 | Dewi Lestari | JavaScript Basics             | 120000 | paid
        8 | Dewi Lestari | React for Beginners           | 250000 | refunded
        9 | Dewi Lestari | Next.js Full Stack            | 400000 | paid
       10 | Rudi Hartono | Python for Data Analysis      | 300000 | paid
       11 | Rina Putri   | SQL Fundamentals              | 150000 | paid
       12 | Rina Putri   | PostgreSQL Performance Tuning | 350000 | pending
       13 | Maya Sari    | Next.js Full Stack            | 400000 | paid
       14 | Budi Santoso | PostgreSQL Performance Tuning | 350000 | paid
(14 rows)

Now we can finally write the Part 5 revenue report with real names. JOIN and GROUP BY work together naturally:

SELECT c.title,
       COUNT(o.id)   AS paid_orders,
       SUM(o.amount) AS revenue
FROM courses c
JOIN orders o ON o.course_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.title
ORDER BY revenue DESC;

Output:

             title             | paid_orders | revenue 
-------------------------------+-------------+---------
 Next.js Full Stack            |           2 |  800000
 SQL Fundamentals              |           3 |  450000
 PostgreSQL Performance Tuning |           1 |  350000
 Python for Data Analysis      |           1 |  300000
 Advanced SQL Queries          |           1 |  275000
 React for Beginners           |           1 |  250000
 JavaScript Basics             |           2 |  240000
(7 rows)

Notice the condition o.status = 'paid' lives in the ON clause here. For an INNER JOIN it does not matter whether you put it in ON or WHERE; for outer joins it matters a lot, as you will see next.

LEFT JOIN

LEFT JOIN keeps every row from the left table, even without a match. Missing right-side columns become NULL. This is how you include users with zero orders:

SELECT u.name,
       COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_orders DESC, u.name;

Output:

     name     | total_orders 
--------------+--------------
 Budi Santoso |            3
 Dewi Lestari |            3
 Andi Wijaya  |            2
 Rina Putri   |            2
 Siti Rahma   |            2
 Maya Sari    |            1
 Rudi Hartono |            1
 Agus Salim   |            0
(8 rows)

We used COUNT(o.id), not COUNT(*). For Agus the joined row exists but o.id is NULL, so COUNT(o.id) correctly returns 0 while COUNT(*) would return 1.

A very common pattern is the anti-join: LEFT JOIN plus WHERE right.id IS NULL finds rows that have no match. Users who have never bought anything:

SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Output:

    name    |      email       
------------+------------------
 Agus Salim | agus@example.com
(1 row)

RIGHT JOIN

RIGHT JOIN is the mirror image: it keeps every row from the right table. Which courses have never been ordered?

SELECT c.title, o.id AS order_id
FROM orders o
RIGHT JOIN courses c ON c.id = o.course_id
WHERE o.id IS NULL;

Output:

              title               | order_id 
----------------------------------+----------
 Docker Essentials for Developers |         
(1 row)

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables (FROM courses c LEFT JOIN orders o ...). Most teams prefer LEFT JOIN everywhere because it reads top-down.

FULL OUTER JOIN

FULL JOIN keeps unmatched rows from both sides. It is perfect for reconciliation reports, like "which courses lack an instructor, and which instructors lack a course?":

SELECT c.title AS course,
       i.name  AS instructor
FROM courses c
FULL JOIN instructors i ON i.id = c.instructor_id
ORDER BY c.id NULLS LAST;

Output:

              course              |   instructor   
----------------------------------+----------------
 SQL Fundamentals                 | Hendra Gunawan
 PostgreSQL Performance Tuning    | Hendra Gunawan
 JavaScript Basics                | Lina Marlina
 React for Beginners              | Lina Marlina
 Advanced SQL Queries             | Hendra Gunawan
 Next.js Full Stack               | Lina Marlina
 Python for Data Analysis         | 
 Docker Essentials for Developers | 
                                  | Joko Prasetyo
(9 rows)

Python and Docker show an empty instructor, and Joko appears with an empty course. A LEFT JOIN would have hidden Joko; a RIGHT JOIN would have hidden the two courses.

Self join

A self join joins a table to itself, using two different aliases. Because prerequisite_id points into courses, we can show each course next to the course you should take first:

SELECT c.title AS course,
       p.title AS prerequisite
FROM courses c
LEFT JOIN courses p ON p.id = c.prerequisite_id
ORDER BY c.id;

Output:

              course              |     prerequisite     
----------------------------------+----------------------
 SQL Fundamentals                 | 
 PostgreSQL Performance Tuning    | Advanced SQL Queries
 JavaScript Basics                | 
 React for Beginners              | JavaScript Basics
 Advanced SQL Queries             | SQL Fundamentals
 Next.js Full Stack               | React for Beginners
 Python for Data Analysis         | 
 Docker Essentials for Developers | 
(8 rows)

We used LEFT JOIN so courses without a prerequisite still appear. Following the chain more than one level (Next.js, then React, then JavaScript) needs a recursive query, which is exactly what we build in Part 7.

Common mistakes

• Forgetting the join condition. Listing two tables without a condition creates a Cartesian product: every row paired with every row. 8 users times 14 orders gives:

SELECT COUNT(*) FROM users, orders;

Output:

 count 
-------
   112
(1 row)

• Filtering the right table in WHERE after a LEFT JOIN. LEFT JOIN orders o ON ... WHERE o.status = 'paid' removes the NULL rows and silently turns your LEFT JOIN into an INNER JOIN. Put that condition in the ON clause instead.
• Using COUNT(*) with outer joins. Count a column from the optional side, like COUNT(o.id).
• Ambiguous column names. Both tables have id; always prefix columns with the table alias.
• Missing indexes on foreign keys. PostgreSQL indexes the referenced primary key automatically, but not the referencing column (orders.user_id). On big tables, add those indexes yourself (Part 8).

Exercise

1. Show each instructor's name and the number of paid orders for their courses, including Joko with 0.
2. List every user with the titles of courses they bought, using string_agg(c.title, ', ') to put the titles in one column.
3. Find courses that nobody has paid for yet (hint: LEFT JOIN with the status condition in ON). You should get only the Docker course.

Summary

Foreign keys turned our loose integers into real, enforced relationships, including a self-reference on courses. You learned INNER, LEFT, RIGHT and FULL joins, the anti-join pattern and self joins, plus why filter placement matters with outer joins.

In Part 7 we tackle multi-step questions with subqueries and CTEs, walk the prerequisite chain with a recursive CTE, and save reusable queries as views.