SQL Self Join, Cross Join and UNION

Beyond the basic joins, SQL has a few more ways to combine data: a self join joins a table to itself, a cross join produces every combination of rows, and UNION stacks the results of two queries on top of each other.

Self Join

Use two aliases of the same table. Here we pair customers who live in the same country. The < condition avoids pairing a customer with themselves and avoids showing each pair twice.

SELECT a.CustomerName AS Customer1,
       b.CustomerName AS Customer2,
       a.Country
FROM Customers a
JOIN Customers b
  ON a.Country = b.Country AND a.CustomerID < b.CustomerID;

Customer1      | Customer2      | Country
---------------+----------------+---------
Alpha Traders  | Blue Sky Foods | Pakistan
Alpha Traders  | Golden Grains  | Pakistan
Blue Sky Foods | Golden Grains  | Pakistan
Cedar Market   | Fresh Mart     | UK
Harbor Goods   | Indigo Retail  | USA
(5 rows)

Cross Join

CROSS JOIN pairs every row of one table with every row of another. 2 categories × 3 cities gives 6 rows. Use it carefully: two tables of 10,000 rows produce 100 million rows.

SELECT c.City, p.Category
FROM (SELECT DISTINCT City FROM Customers WHERE Country = 'Pakistan') c
CROSS JOIN (SELECT DISTINCT Category FROM Products WHERE Category IN ('Grains','Oils')) p
ORDER BY c.City, p.Category;

City      | Category
----------+---------
Islamabad | Grains
Islamabad | Oils
Karachi   | Grains
Karachi   | Oils
Lahore    | Grains
Lahore    | Oils
(6 rows)

UNION

UNION combines the rows of two SELECTs and removes duplicates. Both queries must return the same number of columns with compatible types.

SELECT Country AS Place FROM Customers WHERE Country LIKE 'U%'
UNION
SELECT City FROM Customers WHERE Country = 'UK'
ORDER BY Place;

Place
----------
London
Manchester
UAE
UK
USA
(5 rows)

UNION ALL

UNION ALL keeps duplicates, and is faster because it skips the duplicate check. Compare the row count with the previous example.

SELECT Country AS Place FROM Customers WHERE Country LIKE 'U%'
UNION ALL
SELECT City FROM Customers WHERE Country = 'UK';

Place
----------
UK
UAE
UK
USA
USA
London
Manchester
(7 rows)

Frequently asked questions

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result; UNION ALL keeps them. UNION ALL is faster, so use it when duplicates are impossible or wanted.

What is the difference between JOIN and UNION?

A JOIN adds columns by matching rows from different tables side by side. UNION adds rows by stacking the results of queries with the same columns.

When would I use a self join?

For hierarchies like employees and managers stored in one table, or for comparing rows of the same table, such as finding customers in the same city.