Self joins and CROSS JOIN

Nothing says the two sides of a join must be different tables. A self join uses the same table twice, under two aliases, to compare its rows with each other. A CROSS JOIN has no condition at all: every row of one side with every row of the other.

1SELECT c1.FirstName AS Customer, c2.FirstName AS SameCityAs, c1.City2FROM dbo.Customers AS c13JOIN dbo.Customers AS c24    ON c2.City = c1.City;

Comparing rows of one table

Which customers live in the same city? Take Customers twice, as c1 and c2, and match on City. Without more conditions you also get everyone paired with themself, and every pair twice. c2.CustomerId > c1.CustomerId keeps each pair once:

1SELECT c1.FirstName AS Customer, c2.FirstName AS SameCityAs, c1.City2FROM dbo.Customers AS c13JOIN dbo.Customers AS c24    ON c2.City = c1.City5   AND c2.CustomerId > c1.CustomerId6ORDER BY c1.City, c1.CustomerId, c2.CustomerId;

Customer  SameCityAs  City
--------  ----------  -----
Asha      Chloe       Leeds
Asha      Grace       Leeds
Chloe     Grace       Leeds
Ben       Farid       York

The same pattern finds products that share a price:

1SELECT a.Name, b.Name AS SamePriceAs, a.Price2FROM dbo.Products AS a3JOIN dbo.Products AS b4    ON b.Price = a.Price5   AND b.ProductId > a.ProductId;

Name              SamePriceAs     Price
----------------  --------------  -----
Timber 2x4 2.4 m  Safety goggles   6.80

Hierarchies: a foreign key to the same table

Staff have managers, and managers are staff. So the Staff table gets a ManagerId column that is a foreign key to its own primary key. The owner has no manager, so it allows NULL:

1CREATE TABLE dbo.Staff (2    StaffId   int          NOT NULL PRIMARY KEY,3    Name      nvarchar(40) NOT NULL,4    JobTitle  nvarchar(40) NOT NULL,5    ManagerId int          NULL REFERENCES dbo.Staff (StaffId)6);7INSERT INTO dbo.Staff (StaffId, Name, JobTitle, ManagerId) VALUES8    (1, N'Mira',  N'Owner',           NULL),9    (2, N'Tom',   N'Store manager',   1),10    (3, N'Priya', N'Yard supervisor', 2),11    (4, N'Sam',   N'Sales assistant', 2),12    (5, N'Leo',   N'Yard hand',       3),13    (6, N'Nina',  N'Bookkeeper',      1);14 15SELECT s.Name AS Employee, s.JobTitle, m.Name AS Manager16FROM dbo.Staff AS s17LEFT JOIN dbo.Staff AS m ON m.StaffId = s.ManagerId18ORDER BY s.StaffId;

Employee  JobTitle         Manager
--------  ---------------  -------
Mira      Owner            NULL
Tom       Store manager    Mira
Priya     Yard supervisor  Tom
Sam       Sales assistant  Tom
Leo       Yard hand        Priya
Nina      Bookkeeper       Mira

s is the employee and m is the same table playing the manager. It is a LEFT JOIN so that Mira, who has no manager, is not lost. Each extra level up needs one more join; for a whole tree of any depth there are recursive CTEs, in the next module.

CROSS JOIN: every combination

Sometimes you really do want every combination. A quantity price list for building materials pairs each product with each quantity. The quantities do not live in a table, so a VALUES list in FROM makes a small table on the spot:

1SELECT p.Name, q.Qty, p.Price * q.Qty AS Total2FROM dbo.Products AS p3CROSS JOIN (VALUES (1), (10), (50)) AS q (Qty)4WHERE p.CategoryId = 25ORDER BY p.Name, q.Qty;

Name          Qty   Total
------------  ---  ------
Cement 25 kg    1    7.20
Cement 25 kg   10   72.00
Cement 25 kg   50  360.00
Sand 25 kg      1    4.10
Sand 25 kg     10   41.00
Sand 25 kg     50  205.00

A CROSS JOIN of m rows with n rows returns m × n rows. That is useful for building calendars, grids, and “every store × every product” checklists. It is also what a forgotten join condition produces, which is why the row count is worth a glance whenever a join result looks too big.

1SELECT a.Name, b.Name AS AndAlso, a.Price + b.Price AS Together2FROM dbo.Products AS a3JOIN dbo.Products AS b4    ON b.CategoryId = a.CategoryId5   AND b.ProductId > a.ProductId6WHERE a.Price + b.Price < 20;

Name               AndAlso           Together
-----------------  ----------------  --------
Cement 25 kg       Sand 25 kg           11.30
Wood screws (200)  Wall plugs (100)     16.40