AdventureWorks Database: Return any distinct values from first query, not found on the 2nd query
133. From the following tables write a query in SQL to return any distinct values from first query that aren't also found on the 2nd query.
Sample table: production.Product
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample table: production.WorkOrder
workorderid|productid|orderqty|scrappedqty|startdate |enddate |duedate |scrapreasonid|modifieddate |
-----------+---------+--------+-----------+-----------------------+-----------------------+-----------------------+-------------+-----------------------+
1| 722| 8| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
2| 725| 15| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
3| 726| 9| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
4| 729| 16| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
5| 730| 14| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
6| 732| 16| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
7| 733| 4| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
8| 738| 19| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
9| 741| 2| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
10| 742| 3| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
11| 743| 1| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
12| 745| 1| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
-- more --
Sample Solution:
-- Selecting ProductID values from the Product table that do not exist in the WorkOrder table using the EXCEPT operator
SELECT
-- Selecting the ProductID column from the Product table
ProductID
-- Selecting data from the Product table
FROM
Production.Product
-- Applying the EXCEPT operator to find ProductID values from the Product table that do not exist in the WorkOrder table
EXCEPT
-- Selecting the ProductID column from the WorkOrder table
SELECT
ProductID
-- Selecting data from the WorkOrder table
FROM
Production.WorkOrder ;
Explanation:
- This SQL code retrieves ProductID values from the Product table that do not exist in the WorkOrder table using the EXCEPT operator.
- The first SELECT statement selects ProductID values from the Product table.
- The second SELECT statement selects ProductID values from the WorkOrder table.
- The EXCEPT operator returns only the ProductID values from the first SELECT statement that do not exist in the result set of the second SELECT statement.
- The result set will contain ProductID values from the Product table that are not associated with any work orders in the WorkOrder table.
Sample Output:
productid|
---------+
846|
938|
477|
394|
867|
858|
874|
424|
406|
849|
509|
929|
417|
932|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return any distinct values that are returned by both the queries.
Next: Fetch any distinct values from left query of EXCEPT that aren't present in the query to the right.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.