SQL joins: combining tables

In a well-designed database, the data you want is usually spread across several tables (that's the whole point of keys and relationships from Chapter 4). A join brings those rows back together. Joins are considered a little difficult, so take this chapter slowly.

The example: a WhatsApp-style database

Users and groups have a many-to-many relationship (a user joins many groups, a group has many users), so, as we learned, there are three tables:

SELECT * FROM users;
SELECT * FROM `groups`;
SELECT * FROM membership;

Cartesian product (CROSS JOIN)

The simplest join pairs every row of one table with every row of the other. 7 users × 4 groups = 28 rows.

SELECT * FROM users CROSS JOIN `groups`;

On its own it's rarely useful; most of those pairs are meaningless. It helps when you genuinely need all combinations (e.g. every employee × every shift to build a roster). Its real value is conceptual: every other join is a cross product filtered by a condition.

The four main joins

Think of the two tables as two overlapping circles: the overlap is rows that match on the join condition.

INNER JOIN

Keeps only rows that have a match in both tables. Membership rows for users 5 and 6 vanish (no such users), and so do Abhinav and Rahul (no membership).

SELECT *
FROM membership m
INNER JOIN users u
  ON m.user_id = u.user_id;

• ON gives the join condition, almost always "the common column is equal".
• m and u are short aliases. With them you can say which table a column comes from (m.user_id vs u.user_id).
• In MySQL, plain JOIN means INNER JOIN. Nitish recommends writing INNER anyway for clarity.

LEFT (OUTER) JOIN

All rows of the left table (the one written first), plus matching data from the right; where there's no match, the right side's columns are NULL.

SELECT *
FROM membership m
LEFT JOIN users u
  ON m.user_id = u.user_id;

The memberships of users 5 and 6 now appear, with NULL user details. That's how you'd spot orphaned rows.

RIGHT (OUTER) JOIN

The mirror image: all rows of the right table. Abhinav and Rahul appear, with NULL membership columns.

SELECT *
FROM membership m
RIGHT JOIN users u
  ON m.user_id = u.user_id;

How a join is computed (nested loops)

Nitish explained the algorithm with loops: for each row of the left table, scan every row of the right table; whenever the condition is true, output the combined row. For a LEFT join, if no right row matched, output the left row with NULLs. Step through it:

FULL OUTER JOIN, UNION and UNION ALL

A full outer join keeps everything from both sides. MySQL doesn't support FULL OUTER JOIN (Nitish's attempt errored), so you build it from a left join UNION a right join:

SELECT * FROM membership m LEFT JOIN users u ON m.user_id = u.user_id
UNION
SELECT * FROM membership m RIGHT JOIN users u ON m.user_id = u.user_id;

UNION stacks the results of two queries vertically:

• Both queries must return the same number of columns, with compatible types.
• UNION removes duplicate rows; UNION ALL keeps them (and is faster).

SELECT user_id FROM users
UNION
SELECT user_id FROM membership;        -- each id once

SELECT user_id FROM users
UNION ALL
SELECT user_id FROM membership;        -- repeats kept

Joining three tables

Print every user's name next to the name of each group they're in. The name is in users, the group name in groups, and the link in membership, so join twice:

SELECT u.name, g.group_name
FROM membership m
JOIN users u    ON m.user_id  = u.user_id
JOIN `groups` g ON m.group_id = g.group_id;

You can keep chaining JOIN … ON for 4, 5 or 10 tables. (Also Practice 1, Q10. GROUPS is a reserved word in MySQL 8, hence the backticks.)

Self join

Sometimes a table refers to itself. Each user's emergency_contact is another user's id. To print "user → emergency contact's name", join users with itself under two aliases:

SELECT u1.name AS user, u2.name AS emergency_contact
FROM users u1
JOIN users u2
  ON u1.emergency_contact = u2.user_id;

The same trick finds every employee's manager:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;   -- LEFT keeps the boss (no manager)

SQL lab

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.