0Pricing
SQL Academy · Lesson

Why Joins (Relational Model Recap)

Recap the relational model, normalised tables, foreign keys, and why JOIN is the bridge that reassembles data across tables.

Why Joins (Relational Model Recap) is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Normalisation in One Picture

Instead of duplicating data, you split it across tables:

  • users — id, name, email
  • orders — id, user_id, total

Each order references a user by id, not by storing the user's name and email again.

Why Normalise?

Benefits:

  • No duplication — change a user's email in one place
  • Smaller tables — better cache hit rate
  • Constraints enforce relationships

Foreign Keys Define the Link

The link is declared with a FOREIGN KEY:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL
);

Reassembling Data with JOIN

To show "the user's name on each order", you JOIN the tables back together:

SELECT o.id, o.total, u.full_name, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.id;

The Mental Model: Filtered Cartesian

Conceptually a JOIN:

  1. Forms the cross product of two tables (every row of A paired with every row of B)
  2. Keeps only pairs where the ON condition is TRUE

In practice the database uses hash, merge, or nested-loop join — but the result is the same.

One-to-Many

One user can have many orders. The "many" side carries the foreign key.

-- Each order row points back to its user:
SELECT u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Many-to-Many via Join Table

Tags ↔ Posts: introduce a third table:

CREATE TABLE posts (id BIGSERIAL PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE post_tags (
  post_id BIGINT REFERENCES posts(id),
  tag_id  BIGINT REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

Querying Many-to-Many

Two joins to bring everything together:

SELECT p.title, t.name
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags      t  ON t.id = pt.tag_id
WHERE t.name = 'sql';

When to Denormalise

Denormalisation (duplicating data) is sometimes used for performance — caching computed totals, full-text columns, materialised views. Start normalised; denormalise as you measure.

Joins vs Sub-Queries

Many JOIN queries can be written as subqueries and vice versa. Use whichever reads clearer; modern planners often produce the same plan.

Foreign Keys Are Not Enforced Automatically

You must declare them with REFERENCES. Even with FK constraints, indexing the FK column is YOUR job — declare one explicitly for join performance.

Recap

Joins reassemble data that was split across tables for integrity.

  • Foreign keys declare the link
  • JOIN reverses normalisation at query time
  • One-to-many via FK on the "many" side, many-to-many via join table

Quick Check

In a one-to-many relationship between users and orders, which table carries the foreign key column?

Frequently asked questions

Is the “Why Joins (Relational Model Recap)” lesson free?

Yes — the full text of “Why Joins (Relational Model Recap)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Why Joins (Relational Model Recap)”?

Recap the relational model, normalised tables, foreign keys, and why JOIN is the bridge that reassembles data across tables. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Why Joins (Relational Model Recap)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Why Joins (Relational Model Recap)
  2. INNER JOIN Mechanics
  3. LEFT/RIGHT JOIN and OUTER Joins
  4. Self-Joins and Aliases
← Back to SQL Academy