Complex Joining Logic

Beyond Basic Joins

You're already comfortable linking tables with INNER JOIN and LEFT JOIN. Now we'll explore more advanced ways to combine data. These methods go beyond simple row matching and let you stack datasets, create comprehensive combinations, and analyse relationships within a single table.

Stacking Data Vertically

Sometimes you don't need to combine columns; you need to combine rows. Imagine a database with sales data partitioned into monthly tables, like sales_2023_01, sales_2023_02, and so on. To get a report for the entire first quarter, you need to stack these tables on top of each other.

This is where set operators come in. The UNION operator combines the result sets of two or more SELECT statements. It stacks the rows vertically. An important detail is that UNION automatically removes any duplicate rows from the final result. This is helpful for clean data, but it comes at a cost, as the database has to do extra work to find and eliminate those duplicates.

Then there's UNION ALL. It does the same thing—stacking rows from multiple queries—but it skips the deduplication step. It simply appends all results together, duplicates and all. Because it avoids the check for duplicates, is significantly faster, often 2 to 10 times faster than UNION. It should be your default choice unless you have a specific reason to remove duplicates or know they can't exist.

Rule of thumb: Start with UNION ALL for performance. Only switch to UNION if you explicitly need to remove duplicate rows.

For either operator to work, the SELECT statements must follow two rules:

They must have the same number of columns.
The corresponding columns must have compatible data types.

-- Get first quarter sales, removing any duplicate entries
SELECT product_id, sale_amount FROM sales_2023_01
UNION
SELECT product_id, sale_amount FROM sales_2023_02
UNION
SELECT product_id, sale_amount FROM sales_2023_03;

-- Get all first quarter sales, keeping duplicates (faster)
SELECT product_id, sale_amount FROM sales_2023_01
UNION ALL
SELECT product_id, sale_amount FROM sales_2023_02
UNION ALL
SELECT product_id, sale_amount FROM sales_2023_03;

All Possible Pairs

A CROSS JOIN creates a between two tables. This means it pairs every single row from the first table with every single row from the second table. If table A has 100 rows and table B has 100 rows, a CROSS JOIN between them will produce $100 \times 100 = 10,000$ rows.

This sounds dangerous, and it can be. Use it with caution on large tables. However, it's incredibly useful for specific scenarios, like generating all possible combinations of attributes. For example, if you have a table of T-shirt sizes (S, M, L) and another of colours (Red, Blue), a CROSS JOIN will produce a list of all six possible products you can offer.

size
S
M
L

color
Red
Blue

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

This query would produce a result set perfect for populating a new products table:

size	color
S	Red
S	Blue
M	Red
M	Blue
L	Red
L	Blue

Talking to Yourself

What if the relationship you want to query exists within a single table? This is common in hierarchical data, like an employees table where one column, manager_id, refers to another employee's id in the same table. To get a list of employees and their managers' names, you need to join the table to itself.

This technique is called a SELF JOIN. It's not a new keyword, but rather the practice of listing the same table twice in the FROM clause with different aliases. You then join them on the columns that define the relationship. Using clear is absolutely essential here to avoid ambiguity.

Let's use an employees table as an example.

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1

To find the manager for Bob and Charlie, we can perform a SELF JOIN.

-- We use LEFT JOIN in case an employee has no manager (like a CEO)
SELECT
  e.name AS employee_name,
  m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id;

Here, we treat employees AS e as the table of employees and employees AS m as the table of managers. The JOIN condition e.manager_id = m.id connects an employee to their manager. The result clarifies the hierarchy:

employee_name	manager_name
Alice	NULL
Bob	Alice
Charlie	Alice

Quiz Questions 1/5

What is the primary difference between the UNION and UNION ALL set operators in SQL?

Quiz Questions 2/5

If a table of products has 15 rows and a table of colours has 6 rows, how many rows will be in the result set of a CROSS JOIN between them?

These advanced joining techniques unlock powerful ways to structure and analyse your data, moving far beyond simple one-to-one lookups.

Applied SQL Mastery and Modern Database Design