What is a JOIN?

The receipt problem

You run a small online bookstore. Every day, customers place orders. Your accounting software stores two separate lists: one for customers (name, email, phone) and one for orders (order number, total, date). At month-end, you need a report showing each order alongside the customer who placed it.

You could copy-paste customer names into the orders spreadsheet manually. But that creates duplicated data, and when a customer changes their email, you'd have to update it in dozens of places. Instead, each order stores a single reference: the customer's ID number. To build your report, you match each order's customer ID against the customers list.

That matching operation is what a SQL JOIN does.

---

Why tables are separated

Relational databases split data into multiple tables on purpose. This design principle is called normalization, and it solves three concrete problems:

1. Redundancy: Without normalization, you'd store "Alice, [email protected]" on every single order she places. Fifty orders means fifty copies of her email.
2. Update anomalies: If Alice changes her email, you'd need to update all fifty rows. Miss one, and your data is inconsistent.
3. Delete anomalies: If you delete Alice's only order, you lose her customer record entirely.

Normalization separates concerns: the users table owns user data, the orders table owns order data. Each table has a single responsibility: the same principle you follow in Rails with DDD.

users                      orders
+----+-------+--------+   +----+---------+--------+
| id | name  | email  |   | id | user_id | total  |
+----+-------+--------+   +----+---------+--------+
|  1 | Alice | [email protected] |   | 10 |       1 | 29.99  |
|  2 | Bob   | [email protected] |   | 11 |       1 | 15.50  |
|  3 | Carol | [email protected] |   | 12 |       2 | 42.00  |
+----+-------+--------+   +----+---------+--------+

The user_id column in the orders table is a foreign key: a pointer that says "this order belongs to user #1." Foreign keys are the glue that holds a relational database together.

---

The foreign key contract

A foreign key is more than just a number in a column. It's a contract between two tables:

CREATE TABLE orders (
  id    SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL
    REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL
);

The REFERENCES users(id) clause tells PostgreSQL: "Every value in orders.user_id must exist in users.id." If you try to insert an order with user_id = 999 and no user with id = 999 exists, PostgreSQL rejects the insert with a foreign key violation.

This constraint guarantees referential integrity: you can never have an orphaned order pointing to a nonexistent user. When you write a JOIN, you're relying on this guarantee.

In Rails, this relationship is expressed through ActiveRecord associations. If this app also sold books (it has no orders table), the models would look like this:

# app/models/identity/user.rb
class Identity::User < ApplicationRecord
  has_many :orders
end

# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :user, class_name: "Identity::User"
end

The belongs_to side always holds the foreign key. The has_many side is the referenced table. ActiveRecord uses this mapping to generate JOIN queries behind the scenes.

---

Your first JOIN

To combine data from users and orders, you write a JOIN:

SELECT users.name, orders.id AS order_id,
       orders.total
FROM users
JOIN orders ON orders.user_id = users.id;

Result:

 name  | order_id | total
-------+----------+-------
 Alice |       10 | 29.99
 Alice |       11 | 15.50
 Bob   |       12 | 42.00
(3 rows)

Notice: Carol has no orders, so she doesn't appear. Alice has two orders, so she appears twice. The JOIN didn't invent or remove data: it matched rows based on the condition orders.user_id = users.id and combined the columns from both tables.

---

How the database processes a JOIN

When you submit a JOIN query, the database engine doesn't just "look things up." It follows a multi-step process, and the physical strategy it chooses has a massive impact on performance. There are three main algorithms:

Nested loop join

The simplest approach. For each row in table A, scan every row in table B looking for matches.

For each row in users:           -- outer loop
  For each row in orders:        -- inner loop
    If orders.user_id = users.id:
      Output combined row

With 3 users and 3 orders, that's 3 x 3 = 9 comparisons. With 10,000 users and 100,000 orders, it's 1 billion comparisons. This gets slow fast: unless there's an index on the inner table's join column, which lets the inner loop skip directly to matching rows.

Hash join

The database builds a hash table from the smaller table, then probes it with each row from the larger table.

Step 1: Build hash table from users
  { 1 => Alice, 2 => Bob, 3 => Carol }

Step 2: For each row in orders:
  Look up orders.user_id in hash table
  If found → output combined row

Hash joins are fast for large tables with no useful index. The trade-off is memory: the hash table should fit in work_mem. If it doesn't, PostgreSQL splits the work into batches on disk, which still works but runs slower.

Merge join

Both tables are sorted on the join column, then the database walks through both sorted lists simultaneously, like merging two sorted decks of cards.

users (sorted by id): 1, 2, 3
orders (sorted by user_id): 1, 1, 2

Pointer A → users[1], Pointer B → orders[1]
  Match → output (Alice, order 10)
Pointer B advances → orders[1]
  Match → output (Alice, order 11)
Pointer B advances → orders[2]
  Pointer A advances → users[2]
  Match → output (Bob, order 12)

Merge joins are extremely efficient when both inputs are already sorted (e.g., by an index). They require no extra memory beyond the sort buffers.

Which one does PostgreSQL choose?

The query planner decides. It estimates the cost of each strategy based on table sizes, available indexes, and statistics about data distribution. You can see its choice using EXPLAIN:

EXPLAIN SELECT users.name, orders.total
FROM users
JOIN orders ON orders.user_id = users.id;

You'll learn to read EXPLAIN output in Lesson 6. For now, remember this: JOINs are not magic. They are algorithms with real performance characteristics that depend on your data size and indexing.

---

JOIN syntax: explicit vs. implicit

You may encounter an older syntax that puts the join condition in the WHERE clause:

-- Implicit join (old style, avoid)
SELECT users.name, orders.total
FROM users, orders
WHERE orders.user_id = users.id;

-- Explicit join (modern, preferred)
SELECT users.name, orders.total
FROM users
JOIN orders ON orders.user_id = users.id;

Both produce the same result. But the explicit JOIN ... ON syntax is superior because:

• It separates the join condition (how tables connect) from filter conditions (which rows you want).
• It makes the query easier to read and debug.
• It prevents accidental cross joins (cartesian products) when you forget the WHERE clause.

Always use explicit JOIN syntax.

---

The cartesian product trap

If you write a JOIN without an ON clause (or forget the WHERE in implicit syntax), you get a cartesian product: every row in table A combined with every row in table B:

-- Danger: no join condition
SELECT users.name, orders.total
FROM users
CROSS JOIN orders;

Result: 3 users x 3 orders = 9 rows, most of them nonsensical. With production tables of 100,000 rows each, that's 10 billion rows. This will crash your query or consume all available memory.

---

JOINs in Rails

ActiveRecord provides the joins method to generate INNER JOIN queries:

# Find all users who have at least one order
Identity::User.joins(:orders)
# SQL: SELECT "users".* FROM "users"
#      INNER JOIN "orders"
#      ON "orders"."user_id" = "users"."id"

# Find users with orders over $30
Identity::User.joins(:orders)
              .where(orders: { total: 30.. })
# SQL: SELECT "users".* FROM "users"
#      INNER JOIN "orders"
#      ON "orders"."user_id" = "users"."id"
#      WHERE "orders"."total" >= 30

ActiveRecord reads the has_many :orders association to determine the table name, foreign key, and join condition automatically. You never write raw SQL for standard joins: ActiveRecord handles it.

In this LMS codebase, the same pattern appears everywhere:

# Find all lessons that belong to junior-level courses
Catalog::Lesson.joins(:course)
               .where(courses: { level: :junior })

The joins method generates an INNER JOIN. In Lesson 7, you'll learn about left_joins, includes, and eager_load: each generating different JOIN types for different use cases.

---

Common mistakes

Mistake: Assuming a JOIN always returns the same number of rows as the left table.
Reality: A JOIN can return more rows than either table (when there are multiple matches) or fewer rows (when some rows have no match). Alice has two orders, so she appears twice in the result. Carol has no orders, so she disappears entirely.

Mistake: Forgetting to qualify column names when both tables have a column with the same name.
Fix: Always prefix with the table name: users.id vs orders.id. Ambiguous column references cause PostgreSQL errors.

Mistake: Using a JOIN when you only need to check existence.
Better approach: If you just want to know whether a user has orders (not the order details), use EXISTS or a subquery instead of a JOIN. JOINs bring back columns from both tables: if you don't need them, you're wasting work.

---

Summary

Concept	Key Point
Normalization	Data is split across tables to prevent redundancy and anomalies
Foreign key	A column that references another table's primary key: the glue between tables
JOIN	Combines rows from two tables based on a matching condition
Nested loop join	O(n*m): fast with indexes, slow without
Hash join	Builds a hash table from the smaller table: fast for large unindexed tables
Merge join	Walks two sorted lists simultaneously: fastest when data is pre-sorted
Query planner	Automatically chooses the best join algorithm based on table statistics
Explicit JOIN syntax	Always use JOIN ... ON instead of comma-separated tables with WHERE
Cartesian product	A JOIN without a condition: produces n*m rows, almost always a bug
ActiveRecord joins	Generates INNER JOIN from association definitions: no raw SQL needed