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

*/

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

*/

------------