SQL Self Join, Cross Join and Multiple Table Joins

The joins in the last lesson linked two different tables on a matching column. Real data sometimes asks for something a little different: a table that links to itself, a table that should pair with every row of another, or a question that needs three tables at once. This lesson covers those cases.

Self Join

A self join is a table joined to itself. It sounds odd, but it is the natural way to handle data where one row refers to another row in the same table, like an employee and their manager.

Take this employees table. The manager_id column holds the emp_id of that person’s manager.

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

Output:

Code Explanation:

• The same table is used twice under two aliases, e for the employee and m for the manager.
• Each employee’s manager_id is matched against the emp_id of the manager copy.
• LEFT JOIN keeps Rhea in the result even though she has no manager.

Cross Join

A cross join pairs every row of one table with every row of another, with no matching condition at all. The result has rows equal to the first table’s count multiplied by the second’s.

It is useful for building every combination, such as the sizes and colors of a product.

SELECT s.size, c.color
FROM sizes AS s
CROSS JOIN colors AS c;

With three sizes (S, M, L) and two colors (Black, White), the output has 3 × 2 = 6 rows.

Use it with care. Cross joining two large tables can produce millions of rows and slow your database to a crawl.

Joining More Than Two Tables

To combine three or more tables, keep adding joins one after another. Each JOIN brings in one more table through its own ON rule.

In this example, orders holds IDs only, customers holds names, and products holds product names and prices. Two orders: Neha bought 2 notebooks at 120 each, and Sameer bought 1 desk lamp at 450.

SELECT o.order_id,
       c.name,
       p.product_name,
       o.qty,
       o.qty * p.price AS line_total
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN products  AS p ON o.product_id  = p.product_id;

Output:

Code Explanation:

• orders is the starting point because it holds the keys that link to the other two tables.
• The first join swaps customer_id for the customer’s name.
• The second join replaces product_id with the product name and price.
• o.qty * p.price uses columns from two different tables to calculate each line total.

A plain JOIN means INNER JOIN. You can mix join types in one query, but keep the order clear, since each join works on the result built so far.