AdventureWorks Database: Return NULL if Territoryid has no orders
SQL Query - AdventureWorks: Exercise-168 with Solution
168. From the following tables write a query in SQL to retrieve the SalesOrderid. A NULL is returned if no orders exist for a particular Territoryid. Return territoryid, countryregioncode, and salesorderid. Results are sorted by SalesOrderid, so that NULLs appear at the top.
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 TerritoryID and CountryRegionCode from the salesterritory table, and SalesOrderID from the salesorderheader table. It performs a LEFT OUTER JOIN between the two tables based on the TerritoryID column, and orders the results by SalesOrderID.
-- Selecting TerritoryID and CountryRegionCode from the salesterritory table and assigning the alias 'dst' to the table.
SELECT dst.Territoryid, dst.countryregioncode,
-- Selecting SalesOrderID from the salesorderheader table and assigning the alias 'fis' to the table.
fis.SalesOrderid
-- Joining the salesterritory table (aliased as 'dst') with the salesorderheader table (aliased as 'fis') using a LEFT OUTER JOIN and matching rows where the TerritoryID in both tables are equal.
FROM sales.salesterritory AS dst
LEFT OUTER JOIN sales.salesorderheader AS fis
ON dst.Territoryid = fis.Territoryid
-- Ordering the results by SalesOrderID in ascending order.
ORDER BY fis.SalesOrderid;
Explanation:
- The query retrieves TerritoryID and CountryRegionCode from the salesterritory table and SalesOrderID from the salesorderheader table.
- It performs a LEFT OUTER JOIN between the salesterritory table (aliased as 'dst') and the salesorderheader table (aliased as 'fis') based on the TerritoryID column.
- This LEFT OUTER JOIN ensures that all rows from the salesterritory table are included in the result set, regardless of whether there is a matching row in the salesorderheader table.
- Rows are ordered by SalesOrderID in ascending order. If there is no corresponding SalesOrderID for a territory, it will appear with a NULL value in the SalesOrderID column.
OR
-- This SQL query selects the TerritoryID and CountryRegionCode from the salesterritory table, and the SalesOrderID from the salesorderheader table. It performs a RIGHT OUTER JOIN between the two tables based on the TerritoryID column. It orders the results by SalesOrderID.
-- Selecting TerritoryID and CountryRegionCode from the salesterritory table and SalesOrderID from the salesorderheader table.
SELECT dst.Territoryid, dst.countryregioncode, fis.SalesOrderid
-- Performing a RIGHT OUTER JOIN between the salesorderheader table (aliased as 'fis') and the salesterritory table (aliased as 'dst'), matching rows based on the TerritoryID column.
FROM sales.salesorderheader AS fis
RIGHT OUTER JOIN sales.salesterritory AS dst
ON fis.Territoryid = dst.Territoryid
-- Ordering the results by SalesOrderID.
ORDER BY fis.SalesOrderid;
Explanation:
- The query retrieves the TerritoryID and CountryRegionCode from the salesterritory table and the SalesOrderID from the salesorderheader table.
- A RIGHT OUTER JOIN is used to combine rows from the salesorderheader table (aliased as 'fis') with matching rows from the salesterritory table (aliased as 'dst') based on the TerritoryID column.
- This type of join ensures that all rows from the salesterritory table are included in the result set, with matching rows from the salesorderheader table where available. If there are no matching rows in the salesorderheader table, NULL values are used for the columns from that table.
- The result set is ordered by SalesOrderID.
Sample Output:
territoryid|countryregioncode|salesorderid|
-----------+-----------------+------------+
5|US | 43659|
5|US | 43660|
6|CA | 43661|
6|CA | 43662|
4|US | 43663|
1|US | 43664|
1|US | 43665|
4|US | 43666|
3|US | 43667|
6|CA | 43668|
1|US | 43669|
3|US | 43670|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return all orders with IDs greater than 60000.
Next: Return NULL if there is no match between the two tables.
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