AdventureWorks Database: Return a cross-product of two joined tables
170. From the following tables write a query in SQL to return a cross-product. Order the result set by SalesOrderid.
Sample table: sales.salesterritory
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|
Sample table: sales.salesorderheader
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics