Self Joins and Multi-Table Queries

A self join joins a table to itself — useful whenever a table has a relationship pointing back to its own rows. Our employees table is a perfect example: manager_id points to another row in the same table.

Finding each employee's manager

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

Notice we use a LEFT JOIN, not INNER JOIN — department heads have no manager (manager_id IS NULL), and an INNER JOIN would silently drop them from the results.

Why the aliases matter here

Since both sides of the join come from the same table, you must alias them differently — e for "the employee we're describing" and m for "their manager" — otherwise the database can't tell which employees row you mean in each part of the query.

A richer multi-table example

Let's answer: "for every order, show the customer, the employee who handled it, and that employee's manager":

SELECT
  o.id AS order_id,
  c.customer_name,
  e.first_name AS handled_by,
  m.first_name AS employees_manager
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN employees e ON o.employee_id = e.id
LEFT JOIN employees m ON e.manager_id = m.id;

Joining order_items back to products and orders

To see exactly what was purchased in every order:

SELECT
  o.id AS order_id,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;