AdventureWorks Database: Return a cross-product of two joined tables

SQL Query - AdventureWorks: Exercise-170 with Solution

170. From the following tables write a query in SQL to return a cross-product. Order the result set by SalesOrderid.

territoryid|name          |countryregioncode|group        |salesytd     |saleslastyear|costytd|costlastyear|rowguid                             |modifieddate           |
-----------+--------------+-----------------+-------------+-------------+-------------+-------+------------+------------------------------------+-----------------------+
          1|Northwest     |US               |North America| 7887186.7882| 3298694.4938|      0|           0|43689a10-e30b-497f-b0de-11de20267ff7|2008-04-30 00:00:00.000|
          2|Northeast     |US               |North America| 2402176.8476| 3607148.9371|      0|           0|00fb7309-96cc-49e2-8363-0a1ba72486f2|2008-04-30 00:00:00.000|
          3|Central       |US               |North America|  3072175.118| 3205014.0767|      0|           0|df6e7fd8-1a8d-468c-b103-ed8addb452c1|2008-04-30 00:00:00.000|
          4|Southwest     |US               |North America|10510853.8739| 5366575.7098|      0|           0|dc3e9ea0-7950-4431-9428-99dbcbc33865|2008-04-30 00:00:00.000|
          5|Southeast     |US               |North America| 2538667.2515| 3925071.4318|      0|           0|6dc4165a-5e4c-42d2-809d-4344e0ac75e7|2008-04-30 00:00:00.000|
          6|Canada        |CA               |North America| 6771829.1376|   5693988.86|      0|           0|06b4af8a-1639-476e-9266-110461d66b00|2008-04-30 00:00:00.000|
          7|France        |FR               |Europe       | 4772398.3078| 2396539.7601|      0|           0|bf806804-9b4c-4b07-9d19-706f2e689552|2008-04-30 00:00:00.000|
          8|Germany       |DE               |Europe       | 3805202.3478| 1307949.7917|      0|           0|6d2450db-8159-414f-a917-e73ee91c38a9|2008-04-30 00:00:00.000|
          9|Australia     |AU               |Pacific      | 5977814.9154| 2278548.9776|      0|           0|602e612e-dfe9-41d9-b894-27e489747885|2008-04-30 00:00:00.000|
         10|United Kingdom|GB               |Europe       | 5012905.3656| 1635823.3967|      0|           0|05fc7e1f-2dea-414e-9ecd-09d150516fb5|2008-04-30 00:00:00.000|

Click to view Full table

salesorderid|revisionnumber|orderdate              |duedate                |shipdate               |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal   |taxamt    |freight  |totaldue   |comment|rowguid                             |modifieddate           |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
       43659|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO522145787        |10-4020-000676|     29825|          279|          5|            985|            985|           5|       16281|105041Vi84182         |              | 20565.6206| 1971.5149| 616.0984| 23153.2339|       |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
       43660|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18850127500      |10-4020-000117|     29672|          279|          5|            921|            921|           5|        5618|115213Vi29411         |              |  1294.2529|  124.2483|  38.8276|  1457.3288|       |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
       43661|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18473189620      |10-4020-000442|     29734|          282|          6|            517|            517|           5|        1346|85274Vi6854           |             4| 32726.4786| 3153.7696|  985.553| 36865.8012|       |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
       43662|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18444174044      |10-4020-000227|     29994|          282|          6|            482|            482|           5|       10456|125295Vi53935         |             4| 28832.5289| 2775.1646| 867.2389| 32474.9324|       |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
       43663|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18009186470      |10-4020-000510|     29565|          276|          4|           1073|           1073|           5|        4322|45303Vi22691          |              |   419.4589|   40.2681|  12.5838|   472.3108|       |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
       43664|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16617121983      |10-4020-000397|     29898|          280|          1|            876|            876|           5|         806|95555Vi4081           |              | 24432.6088| 2344.9921|   732.81| 27510.4109|       |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
       43665|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16588191572      |10-4020-000146|     29580|          283|          1|            849|            849|           5|       15232|35568Vi78804          |              | 14352.7713| 1375.9427| 429.9821| 16158.6961|       |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
	   -- more --

Click to view Full table

Sample Solution:

-- This SQL query selects the TerritoryID from the salesterritory table and the SalesOrderID from the salesorderheader table. It performs a CROSS JOIN between the two tables, generating a Cartesian product of all rows from both tables. It orders the results by SalesOrderID.
-- Selecting TerritoryID from the salesterritory table and SalesOrderID from the salesorderheader table.
SELECT dst.Territoryid, fis.SalesOrderid  
-- Performing a CROSS JOIN between the salesterritory table (aliased as 'dst') and the salesorderheader table (aliased as 'fis').
FROM sales.salesterritory  AS dst 
CROSS JOIN sales.salesorderheader  AS fis  
-- Ordering the results by SalesOrderID.
ORDER BY fis.SalesOrderid;

Explanation:

• The query retrieves the TerritoryID from the salesterritory table (aliased as 'dst') and the SalesOrderID from the salesorderheader table (aliased as 'fis').
• A CROSS JOIN is used to combine each row from the salesterritory table with every row from the salesorderheader table, resulting in a Cartesian product.
• Unlike other join types, CROSS JOIN does not use any condition to match rows from the two tables; it simply combines all possible combinations of rows.
• The result set contains all combinations of TerritoryID and SalesOrderID from the two tables.
• The results are ordered by SalesOrderID.

Sample Output:

territoryid|salesorderid|
-----------+------------+
          1|       43659|
          2|       43659|
          3|       43659|
          4|       43659|
          5|       43659|
          6|       43659|
          7|       43659|
          8|       43659|
          9|       43659|
         10|       43659|
          1|       43660|
          2|       43660|
...		

Contribute your code and comments through Disqus.

Previous: Return NULL if there is no match between the two tables.
Next: Customers with BirthDate after January 1, 1970 and last name Smith.