AdventureWorks Database: Return NULL if there is no match between the two tables
SQL Query - AdventureWorks: Exercise-169 with Solution
169. From the following table write a query in SQL to return all rows from both joined tables but returns NULL for values that do not match from the other table. Return territoryid, countryregioncode, and salesorderid. Results are sorted 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 and CountryRegionCode from the salesterritory table, and the SalesOrderID from the salesorderheader table. It performs a FULL 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 FULL OUTER JOIN between the salesterritory table (aliased as 'dst') and the salesorderheader table (aliased as 'fis'), matching rows based on the TerritoryID column.
FROM sales.salesterritory AS dst
FULL OUTER JOIN sales.salesorderheader AS fis
ON dst.Territoryid = fis.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 FULL OUTER JOIN is used to combine all rows from both the salesterritory table (aliased as 'dst') and the salesorderheader table (aliased as 'fis'), matching rows based on the TerritoryID column.
- This type of join ensures that all rows from both tables are included in the result set. If there are no matching rows in either table, NULL values are used for the columns from the table without a match.
- The result set is ordered by SalesOrderID.
OR
-- This SQL query selects the TerritoryID and CountryRegionCode from the salesterritory table, and the SalesOrderID from the salesorderheader table. It performs a FULL 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 FULL JOIN between the salesterritory table (aliased as 'dst') and the salesorderheader table (aliased as 'fis'), matching rows based on the TerritoryID column.
FROM sales.salesterritory AS dst
FULL JOIN sales.salesorderheader AS fis
ON dst.Territoryid = fis.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 FULL JOIN is used to combine all rows from both the salesterritory table (aliased as 'dst') and the salesorderheader table (aliased as 'fis'), matching rows based on the TerritoryID column.
- This type of join ensures that all rows from both tables are included in the result set. If there are no matching rows in either table, NULL values are used for the columns from the table without a match.
- 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|
1|US | 43671|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return NULL if Territoryid has no orders.
Next: Return a cross-product of two joined 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