FULL OUTER JOIN and CROSS JOIN

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Keep unmatched rows from either input and construct all pairings deliberately.

Run the example

WITH SelectedCategories AS (
    SELECT CategoryID FROM dbo.Categories WHERE CategoryID IN (1, 5)
), SelectedProducts AS (
    SELECT ProductID, CategoryID FROM dbo.Products WHERE ProductID IN (1, 6)
)
SELECT c.CategoryID AS SelectedCategoryID,
       p.ProductID, p.CategoryID AS ProductCategoryID
FROM SelectedCategories AS c
FULL OUTER JOIN SelectedProducts AS p ON p.CategoryID = c.CategoryID
ORDER BY COALESCE(c.CategoryID, p.CategoryID), p.ProductID;

Read it step by step

1. WITH defines two common table expressions (CTEs): named query results that exist only for this one statement. Chapter 7 covers CTEs in full. Here they select two categories and two products from the canonical tables.
2. FULL OUTER JOIN retains matches plus rows that appear on only one side.
3. Here the selected ID columns cannot be NULL in their source tables, so a NULL ID in the result identifies an unmatched side. COALESCE in ORDER BY gives a repeatable display order; CROSS JOIN instead makes every possible pair.

Check the result

Product 6 has ProductCategoryID 2, absent from the selected category subset; Category 5 has no product in the selected product subset. Neither unmatched row implies a broken foreign key.

Practice

CROSS JOIN Departments 1–2 with Categories 1–2. How many pairs appear?

WITH D AS (SELECT DepartmentID FROM dbo.Departments WHERE DepartmentID <= 2),
     C AS (SELECT CategoryID FROM dbo.Categories WHERE CategoryID <= 2)
SELECT D.DepartmentID, C.CategoryID
FROM D CROSS JOIN C
ORDER BY D.DepartmentID, C.CategoryID;