w3resource

AdventureWorks Database: Return any distinct values from first query, not found on the 2nd query

SQL Query - AdventureWorks: Exercise-133 with Solution

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 --

Click to view Full table

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 --

Click to view Full table

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-133.php