Multi-Table Joins and Self-Joins

Pull a coherent picture out of many tables -- including a table joined to itself.

Big idea: A normalised database spreads facts across many small tables. A real question -- "which sales rep sold which product to which customer?" -- lives in three or four of them at once. You answer it by chaining joins: each JOIN ... ON bolts one more table onto the result. A self-join is the same trick applied to a single table twice, which is how you connect a row to another row in the same table (an employee to their manager).

By the end you will be able to:

chain three or more joins cleanly with table aliases,
reason about join order -- when it matters and when it does not,
write a self-join to link a row to another row in the same table,
build pair queries and avoid duplicate/mirror pairs,
recognise CROSS JOIN -- both the deliberate kind and the accidental Cartesian explosion.

This builds on the beginner course: you already know SELECT, WHERE, GROUP BY, keys, and single INNER JOIN / LEFT JOIN with NULL.

1. The example schema we will use

Every query below runs against one small shop database. A shop naturally has customers, the orders they place, the products on each order, the employees who serve them, and the departments those employees belong to. Two of these relationships are worth noting up front: orders.emp_id links an order to the rep who took it, and employees.manager_id points back into the same table -- that column is what makes self-joins possible.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(40)
);

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(40),
  manager_id INT,                 -- points at another employees.emp_id
  dept_id    INT,
  FOREIGN KEY (manager_id) REFERENCES employees(emp_id),
  FOREIGN KEY (dept_id)    REFERENCES departments(dept_id)
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(40),
  city        VARCHAR(40)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  emp_id      INT,                -- the sales rep who took the order
  order_date  DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (emp_id)      REFERENCES employees(emp_id)
);

CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(40),
  price        DECIMAL(8,2)
);

CREATE TABLE order_items (
  order_id   INT,
  product_id INT,
  quantity   INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

A handful of rows to query:

INSERT INTO departments VALUES
  (1,'Sales'), (2,'Support'), (3,'Warehouse');

INSERT INTO employees VALUES         -- (emp_id, name, manager_id, dept_id)
  (1,'Alice', NULL, 1),              -- top manager, no boss
  (2,'Bob',   1,    1),
  (3,'Carol', 1,    2),
  (4,'Dan',   2,    1),
  (5,'Eve',   3,    2);

INSERT INTO customers VALUES
  (1,'Maria','Bishkek'), (2,'John','Almaty'), (3,'Sara','Osh');

INSERT INTO orders VALUES            -- (order_id, customer_id, emp_id, order_date)
  (101,1,2,'2026-03-01'),
  (102,2,2,'2026-03-02'),
  (103,1,4,'2026-03-05'),
  (104,3,3,'2026-03-06');

INSERT INTO products VALUES
  (1,'Keyboard',25.00), (2,'Mouse',15.00), (3,'Monitor',120.00);

INSERT INTO order_items VALUES       -- (order_id, product_id, quantity)
  (101,1,2), (101,2,1),
  (102,3,1),
  (103,2,3),
  (104,1,1);

The shop schema. Each arrow is a foreign key; the orange loop is the self-reference used for self-joins.

Keep this diagram in mind: a join always follows one of these arrows, matching a foreign-key column in one table to the primary key it references in another.

Multi-Table Joins and Self-Joins

Multi-Table Joins and Self-Joins

1. The example schema we will use

Continue this lesson on YourAIZone

In the full lesson