Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the creation of Cartesian products with CROSS JOIN.

-- MSSQL Cartesian product
-- Microsoft T-SQL cross join create a Cartesian product of two tables
USE AdventureWorks;
SELECT TOP 100 * FROM Sales.Customer CROSS JOIN Sales.SalesOrderHeader
GO
/* Partial results – all columns from both tables combined
 
CustomerID  TerritoryID AccountNumber     CustomerType      rowguid     ModifiedDate      SalesOrderID      RevisionNumber    OrderDate   DueDate     ShipDate    Status      OnlineOrderFlag   SalesOrderNumber  PurchaseOrderNumber     AccountNumber      CustomerID  ContactID   SalesPersonID     TerritoryID BillToAddressID   ShipToAddressID      ShipMethodID      CreditCardID      CreditCardApprovalCode  CurrencyRateID    SubTotal      TaxAmt      Freight     TotalDue    Comment     rowguid     ModifiedDate
1     1     AW00000001  S     3F5AE95E-B87D-4AED-95B4-C3797AFCB74F      2004-10-13 11:15:07.263      43659 1     2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 2001-07-08 00:00:00.000      5     0     SO43659     PO522145787 10-4020-000676    676   378   279   5      985   985   5     16281 105041Vi84182     NULL  24643.9362  1971.5149   616.0984      27231.5495  NULL  79B65321-39CA-4115-9CBA-8FE0903E12E6      2001-07-08 00:00:00.000
 
*/

-- MSSQL Cartesian Product - old-fashioned syntax
USE AdventureWorks;
SELECT TOP 100 * FROM Sales.Customer, Sales.SalesOrderHeader
GO

------------
 
SELECT Records=COUNT(*) FROM Sales.Customer
GO
-- 19185
SELECT Records=COUNT(*) FROM  Sales.SalesOrderHeader
GO
-- 31465
SELECT Records=COUNT(*), CartesianProduct=19185 * 31465
FROM Sales.Customer CROSS JOIN Sales.SalesOrderHeader
GO
/*
Records           CartesianProduct
603656025         603656025

*/
-- SQL CROSS JOIN does not take an ON term - following will FAIL
SELECT Records=COUNT(*)
FROM Sales.Customer c
CROSS JOIN Sales.SalesOrderHeader soh
      ON c.CustomerID = soh.CustomerID
GO
/* Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'ON'.

*/

------------
-- SQL cross join to create all possible combinations
-- SQL combinations - Cartesian products
DECLARE  @Coin  TABLE(
                      Side CHAR(4)
                      )
INSERT @Coin SELECT 'head';
INSERT @Coin SELECT 'tail'
 
-- Single flip of a coin combinatorics
SELECT * FROM   @Coin
 
/*
head
tail
*/
 
-- Two flips of a coin combinatorics
SELECT *
FROM   @Coin a
       CROSS JOIN @Coin b
 
/*
head  head
tail  head
head  tail
tail  tail
*/
 
-- Three flips of a coin combinatorics
SELECT *
FROM   @Coin a
       CROSS JOIN @Coin b
       CROSS JOIN @Coin c
GO
/*
head  head  head
head  head  tail
tail  head  head
tail  head  tail
head  tail  head
head  tail  tail
tail  tail  head
tail  tail  tail

*/

------------
------------
-- SQL hexadecimal
------------
-- Microsoft SQL cte - Common Table Expression 
-- SQL hexadecimal sequence generation by Cartesian product
-- Create a cte to give the sequence of 0-F hex digits
WITH cteDigits
     AS (SELECT '0' AS Digit
         UNION
         SELECT '1'   UNION  SELECT '2'  UNION
         SELECT '3'   UNION  SELECT '4'  UNION
         SELECT '5'   UNION  SELECT '6'  UNION
         SELECT '7'   UNION  SELECT '8'  UNION
         SELECT '9'   UNION  SELECT 'A'  UNION
         SELECT 'B'   UNION  SELECT 'C'  UNION
         SELECT 'D'   UNION  SELECT 'E'  UNION
         SELECT 'F'  
         ),
     -- Create a second cte to generate a 1,048,576 number sequence
     cteHex5
     AS (SELECT SeqNo = hex65536.Digit+
     hex4096.Digit + hex256.Digit  + hex16.Digit
     + hex.Digit
         FROM   cteDigits AS hex
                -- SQL cross join
                CROSS JOIN cteDigits AS hex16
                CROSS JOIN cteDigits AS hex256
                CROSS JOIN cteDigits AS hex4096
                CROSS JOIN cteDigits AS hex65536)
               
-- Main query
-- T-SQL random select from cte
SELECT TOP 10 SeqNo
FROM     cteHex5
ORDER BY NEWID()
GO
/* Results
SeqNo
FDAA2
724B9
240DE
CAF5A
DBEB4
E720B
888DB
9A26C
CA838
D7BB5
*/
 
------------

Related articles:

How to construct INNER JOIN with a GROUP BY?

Oracle - Cartesian Product - sql cross join