SQL Intermediate
Advanced Joins
Joining a Table to Itself
You already know how to join different tables together. But what if the data you need to connect is all in the same table? This is where a SELF JOIN comes in. It's a regular join, but you're joining a table with itself.
This sounds strange, but it's perfect for hierarchical data. A classic example is an employee table that lists each employee and their manager. The manager is also an employee, so their information is in the same table.
To make this work, you have to treat the table as if it were two separate tables. We do this by giving the table two different aliases. Let's look at an Employees table:
| EmployeeID | Name | ManagerID |
|---|---|---|
| 1 | Alice Smith | 3 |
| 2 | Bob Johnson | 3 |
| 3 | Carol White | NULL |
| 4 | David Green | 1 |
We want to see a list of each employee and the name of their manager. We can join the Employees table to itself, using one alias for the employee (e) and another for the manager (m). The join condition connects the employee's ManagerID to the manager's EmployeeID.
SELECT
e.Name AS EmployeeName,
m.Name AS ManagerName
FROM
Employees e
INNER JOIN
Employees m ON e.ManagerID = m.EmployeeID;
The result links the rows within the same table.
| EmployeeName | ManagerName |
|---|---|
| Alice Smith | Carol White |
| Bob Johnson | Carol White |
| David Green | Alice Smith |
Notice Carol White isn't in the result because her
ManagerIDisNULL, so theINNER JOINcondition fails. If you wanted to include her, you could use aLEFT JOINinstead.
Getting the Full Picture
Sometimes you need to combine two tables and see everything from both, whether or not there's a match. LEFT JOIN gives you everything from the left table, and RIGHT JOIN gives you everything from the right. A FULL OUTER JOIN gives you everything from both.
It returns all rows from both tables. Where a match exists, the data is combined. Where a match doesn't exist in one of the tables, NULL values are used to fill in the gaps.
Imagine we have two tables for customer contacts: one for email subscribers and one for phone contacts.
This join is useful for data reconciliation, where you need to find discrepancies between two sets of data.
SELECT
COALESCE(e.CustomerID, p.CustomerID) AS CustomerID,
e.Email,
p.Phone
FROM
EmailSubscribers e
FULL OUTER JOIN
PhoneContacts p ON e.CustomerID = p.CustomerID;
We use the COALESCE function here to pick the first non-NULL CustomerID from either table, giving us a clean master list of all customers.
Creating All Possible Combinations
A CROSS JOIN creates a Cartesian product, which is a mathematical way of saying it pairs every single row from the first table with every single row from the second table. Unlike other joins, a CROSS JOIN doesn't need a join condition (ON clause).
Be careful! If you cross join a table with 1,000 rows and another with 1,000 rows, you'll get 1,000,000 rows in your result. Use it only when you specifically need every combination.
Let's say you're launching a new t-shirt and want to generate a list of all possible variations. You have a TShirts table with colors and a Sizes table with sizes.
| Color |
|---|
| Red |
| Blue |
| Green |
| Size |
|---|
| S |
| M |
| L |
A CROSS JOIN will give you every combination of color and size.
SELECT
t.Color,
s.Size
FROM
TShirts t
CROSS JOIN
Sizes s;
This produces a complete list of all 9 product variations, which you could then use to populate a master products table or for inventory planning.
What is the primary purpose of using a SELF JOIN in SQL?
Which type of join would you use to generate a complete list of all possible t-shirt variations from one table of colors and another table of sizes?
Mastering these advanced joins gives you more tools to solve complex data puzzles. By understanding how they work, you can combine data in more flexible and powerful ways.