Advanced Joins

Beyond the Basics

You're already familiar with INNER JOIN for finding matching records and LEFT JOIN for keeping all records from the left table. Now, let's explore some more specialised joins that can solve complex data puzzles. These advanced joins give you greater power to combine tables in sophisticated ways.

Self Join: The Table in the Mirror

A self-join is not a different type of join, but rather a technique where you join a table to itself. It's useful for querying hierarchical data or comparing rows within the same table.

Imagine an Employees table where each employee has an ID and a ManagerID that points to another employee's ID in the same table. How would you find the name of each employee's manager? You can't do it in a single pass. You need to treat the table as two separate entities: one for the employees and one for the managers.

To do this, you use aliases. By giving the Employees table two different names in the same query (e.g., e for employee and m for manager), you can join them as if they were two different tables.

SELECT
    e.EmployeeName AS Employee,
    m.EmployeeName AS Manager
FROM
    Employees e
INNER JOIN -- Or LEFT JOIN to include employees without a manager
    Employees m ON e.ManagerID = m.EmployeeID;

The key is using table aliases (e and m) to create a clear relationship between the employee row and the corresponding manager row within the same table.

Cross Join: Every Possible Combination

A CROSS JOIN creates a Cartesian product, which is a fancy way of saying it matches every row from the first table with every row from the second table. If you have a table of T-shirt sizes and another of colours, a CROSS JOIN can generate a list of every possible size-and-colour combination.

This join doesn't need an ON clause because it isn't looking for matching keys. It's simply pairing everything up.

SELECT
    s.Size,
    c.Colour
FROM
    Sizes s
CROSS JOIN
    Colours c;

If the Sizes table has 3 rows (S, M, L) and the Colours table has 2 rows (Red, Blue), the result will have 3 * 2 = 6 rows.

Size	Colour
S	Red
S	Blue
M	Red
M	Blue
L	Red
L	Blue

Be careful with CROSS JOIN. Applying it to large tables can generate an enormous number of rows and slow your system to a crawl.

Full Outer Join: The Best of Both Worlds

While an INNER JOIN shows only the matching rows and a LEFT JOIN includes all rows from the left table, a FULL OUTER JOIN includes all rows from both tables. It's the most inclusive join.

When a row from one table doesn't have a match in the other, the columns from the other table will be filled with NULL values. This is perfect for when you need a complete picture of two datasets, including the parts that don't overlap.

For example, let's say you have a Customers table and a Suppliers table, both with city information. You want a list of all cities where you have either a customer or a supplier (or both).

SELECT
    c.CustomerName,
    s.SupplierName,
    COALESCE(c.City, s.City) AS City -- COALESCE picks the first non-NULL value
FROM
    Customers c
FULL OUTER JOIN
    Suppliers s ON c.City = s.City;

This query would return a list including:

Cities with both customers and suppliers.
Cities with only customers (SupplierName will be NULL).
Cities with only suppliers (CustomerName will be NULL).

Quiz Questions 1/4

You have a single table named Employees where each employee has an EmployeeID and a ManagerID that refers to another employee's ID within the same table. Which join technique is most suitable for retrieving a list of employees and their corresponding manager's name from this single table?

Quiz Questions 2/4

If you perform a CROSS JOIN between a TShirts table with 5 different sizes and a Colours table with 8 different colours, how many rows will the resulting table have?

Mastering these joins allows you to handle complex relationships and comparisons within your data, giving you a much more powerful SQL toolkit.

Mastering SQL for Analytics