Joining tables

Introduction

A good design keeps each fact in one table: teachers in one, departments in another, linked by dept_id. To see a teacher together with their department's name, a query joins the two tables.

Good designs split data across tables, so almost every real question needs a join. Learning to choose the right join, and to predict its rows, is the single most important SQL skill.

How it works

CROSS JOIN: every pair

CREATE TABLE colours (colour TEXT);
CREATE TABLE sizes (size TEXT);
INSERT INTO colours VALUES ('red'), ('blue'), ('green');
INSERT INTO sizes VALUES ('M'), ('L');

SELECT colour, size FROM colours CROSS JOIN sizes;

CROSS JOIN pairs every row of one table with every row of the other: 3 colours × 2 sizes = 6 rows. This is the Cartesian product, and every other join is a Cartesian product with only some of the pairs kept.

INNER JOIN: the pairs that match

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id)
);
INSERT INTO departments (name) VALUES ('CSE'), ('EEE'), ('Physics');
INSERT INTO teachers (name, dept_id) VALUES
  ('Dr. Alam', 1), ('Ms. Sultana', 1), ('Mr. Hasan', 2), ('Ms. Rina', NULL);

SELECT t.name AS teacher, d.name AS department
FROM teachers t
JOIN departments d ON d.id = t.dept_id;

SELECT d.name AS department, t.name AS teacher
FROM departments d
LEFT JOIN teachers t ON t.dept_id = d.id
ORDER BY d.name;

SELECT t.name, d.name
FROM teachers t
JOIN departments d ON d.id = t.dept_id;

• JOIN ... ON condition keeps only the pairs where the condition is true. JOIN on its own means INNER JOIN.
• teachers t gives the table a short alias, so t.name means the teacher's name. When both tables have a column with the same name (name, id), you must say which one you mean, or you get an ambiguous column error.
• Ms. Rina has no department (NULL), so no pair matches her and she is not in the result. Physics has no teachers, so it is not in the result either.

LEFT JOIN: keep every row of the left table

LEFT JOIN (or LEFT OUTER JOIN) keeps every row of the first table. A row with no match is kept once, with NULL in the other table's columns. The second query in the example lists every department, Physics with a NULL teacher.

That gives a common trick: rows with no match are the ones where the other side is NULL.

-- Departments with no teachers
SELECT d.name FROM departments d LEFT JOIN teachers t ON t.dept_id = d.id WHERE t.id IS NULL;

RIGHT JOIN keeps every row of the second table, and FULL OUTER JOIN keeps every row of both. (SQLite has them since version 3.39; MySQL has no FULL OUTER JOIN.)

More than two tables

Joins chain: FROM enrollments e JOIN students s ON s.id = e.student_id JOIN courses c ON c.code = e.course_code. The lesson on designing databases uses exactly this.

Syntax

SELECT t.name AS teacher, d.name AS department
FROM teachers t
LEFT JOIN departments d ON d.id = t.dept_id;

Example

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
  (1, 'Farhana', NULL),
  (2, 'Imran', 1),
  (3, 'Joya', 1),
  (4, 'Kamal', 2);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Each employee's manager_id is another employee's id. Using the same table twice, under two aliases (e for the employee, m for the manager), pairs each employee with their manager. The LEFT JOIN keeps the boss, who has no manager.

Line by line

1. employees has an id and a manager_id, which is another employee's id.
2. Farhana has no manager, Imran and Joya report to Farhana, and Kamal reports to Imran.
3. The query uses the table twice: e for each employee and m for their manager.
4. ON m.id = e.manager_id pairs each employee with the row of their manager.
5. LEFT JOIN keeps Farhana even though no manager row matches, so her manager is NULL.

Expected result

employee  manager
--------  -------
Farhana   NULL
Imran     Farhana
Joya      Farhana
Kamal     Imran
(4 rows)

In the real world

Every page that shows an order with its customer's name, or a post with its author, runs a join. Organisation charts, family trees and replies to comments are stored with a column that points back into the same table, and read with self-joins.

Common mistakes

• Leaving out the ON condition, which gives a Cartesian product with far too many rows.
• Using an INNER JOIN where rows without a match must stay, and silently losing them.
• Writing a column that exists in both tables without a table name, which is an ambiguous column error.

Best practices

• Give every table an alias, and qualify every column in a join.
• Ask whether rows without a match should appear, and choose INNER or LEFT on purpose.
• Count the rows you expect before running a join on a large table.

Summary

Joins combine tables. CROSS JOIN pairs every row, INNER JOIN keeps matching pairs, and LEFT JOIN keeps every row of the first table, which also finds rows without a match. Aliases let a table be joined with itself.

Mark lesson complete