SQL Self Join, Cross Join and Multi-Table Joins

Two more join patterns complete your toolkit. A self join joins a table to itself – the classic "show each employee with their manager" interview question. A cross join pairs every row with every other row, useful for generating combinations. Finally, you'll put it all together in a four-table query of the kind real reports use.

Self join: employees and managers

In employees, manager_id points to another row in the same table. To show both names, use the table twice with two aliases:

SELECT e.name       AS employee,
       e.department,
       m.name       AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

+--------------+-------------+-------------+
| employee     | department  | manager     |
+--------------+-------------+-------------+
| Anita Desai  | Management  | NULL        |
| Rohit Mehta  | Engineering | Anita Desai |
| Sana Iqbal   | Engineering | Rohit Mehta |
| Dev Patel    | Engineering | Rohit Mehta |
| Priya Sharma | Engineering | Rohit Mehta |
| Farhan Ali   | Sales       | Anita Desai |
| Lata Joshi   | Sales       | Farhan Ali  |
| Tom Mathew   | Sales       | Farhan Ali  |
| Kavya Reddy  | HR          | Anita Desai |
| Imran Shaikh | HR          | Kavya Reddy |
+--------------+-------------+-------------+

Think of e and m as two copies of the table: one playing "employee", one playing "manager". A LEFT JOIN keeps Anita, who has no manager.

Employees paid close to their manager

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary >= m.salary * 0.7;          -- at least 70% of their manager's pay

+--------------+--------+-------------+----------------+
| employee     | salary | manager     | manager_salary |
+--------------+--------+-------------+----------------+
| Rohit Mehta  | 180000 | Anita Desai |         250000 |
| Imran Shaikh |  65000 | Kavya Reddy |          90000 |
+--------------+--------+-------------+----------------+

Team size per manager

SELECT m.name AS manager, COUNT(e.emp_id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.name
ORDER BY direct_reports DESC, manager;

+-------------+----------------+
| manager     | direct_reports |
+-------------+----------------+
| Anita Desai |              3 |
| Rohit Mehta |              3 |
| Farhan Ali  |              2 |
| Kavya Reddy |              1 |
+-------------+----------------+

Pairs within a group

-- Customers who live in the same city (each pair once)
SELECT a.name AS customer_1, b.name AS customer_2, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.customer_id < b.customer_id;

+------------+------------+-------+
| customer_1 | customer_2 | city  |
+------------+------------+-------+
| Asha Verma | Vikram Das | Pune  |
| Ravi Kumar | Zoya Khan  | Delhi |
+------------+------------+-------+

a.customer_id < b.customer_id stops a customer pairing with themselves and stops each pair appearing twice (A–B and B–A).

CROSS JOIN: every combination

SELECT s.size, c.colour
FROM (SELECT 'S' AS size UNION ALL SELECT 'M' UNION ALL SELECT 'L') AS s
CROSS JOIN (SELECT 'Black' AS colour UNION ALL SELECT 'White') AS c
ORDER BY c.colour, FIELD(s.size, 'S', 'M', 'L');

+------+--------+
| size | colour |
+------+--------+
| S    | Black  |
| M    | Black  |
| L    | Black  |
| S    | White  |
| M    | White  |
| L    | White  |
+------+--------+

3 sizes × 2 colours = 6 variants. Cross joins are handy for generating product variants, calendars (every day × every store) or test data. The result size is rows(A) × rows(B), so be careful with big tables.

Joining four tables

-- Everything Asha bought: order, date, product, category, quantity and amount
SELECT o.order_id,
       o.order_date,
       p.name                         AS product,
       COALESCE(cat.name, '(none)')   AS category,
       i.quantity,
       i.quantity * i.unit_price      AS amount
FROM customers c
JOIN orders o           ON o.customer_id = c.customer_id
JOIN order_items i      ON i.order_id = o.order_id
JOIN products p         ON p.product_id = i.product_id
LEFT JOIN categories cat ON cat.category_id = p.category_id
WHERE c.name = 'Asha Verma'
ORDER BY o.order_date, p.name;

+----------+------------+-------------------+-------------+----------+--------+
| order_id | order_date | product           | category    | quantity | amount |
+----------+------------+-------------------+-------------+----------+--------+
|      101 | 2025-08-02 | Gel Pen Pack      | Stationery  |        2 | 240.00 |
|      101 | 2025-08-02 | Wireless Mouse    | Electronics |        1 | 599.00 |
|      103 | 2025-08-19 | Clean Code        | Books       |        1 | 450.00 |
|      103 | 2025-08-19 | SQL in 10 Minutes | Books       |        1 | 350.00 |
|      109 | 2025-10-21 | A5 Notebook       | Stationery  |        2 | 180.00 |
|      109 | 2025-10-21 | Gel Pen Pack      | Stationery  |        3 | 360.00 |
+----------+------------+-------------------+-------------+----------+--------+

Non-equi joins

Joins don't have to use =. Here each employee is matched to a salary band by range:

SELECT e.name, e.salary, b.band
FROM employees e
JOIN (SELECT 'Band A' AS band, 0 AS low, 89999 AS high
      UNION ALL SELECT 'Band B', 90000, 149999
      UNION ALL SELECT 'Band C', 150000, 999999) AS b
  ON e.salary BETWEEN b.low AND b.high
WHERE e.department IN ('HR', 'Management')
ORDER BY e.salary DESC;

+--------------+--------+--------+
| name         | salary | band   |
+--------------+--------+--------+
| Anita Desai  | 250000 | Band C |
| Kavya Reddy  |  90000 | Band B |
| Imran Shaikh |  65000 | Band A |
+--------------+--------+--------+

Practice

Summary

Interview practice: SQL queries asked in interviews.