AdventureWorks Database: Any distinct businessentityid from the first query not found in the second query

SQL Query - AdventureWorks: Exercise-136 with Solution

136. From the following table write a query which is the combination of two queries. Return any distinct businessentityid from the 1st query that aren't also found in the 2nd query. Sort the result set in ascending order on businessentityid.

businessentityid|rowguid                             |modifieddate           |
----------------+------------------------------------+-----------------------+
               1|0c7d8f81-d7b1-4cf0-9c0a-4cd8b6b50087|2017-12-13 13:20:24.150|
               2|6648747f-7843-4002-b317-65389684c398|2017-12-13 13:20:24.430|
               3|568204da-93d7-42f4-8a7a-4446a144277d|2017-12-13 13:20:24.540|
               4|0eff57b9-4f4f-41a6-8867-658c199a5fc0|2017-12-13 13:20:24.570|
               5|b82f88d1-ff79-4fd9-8c54-9d24c140f647|2017-12-13 13:20:24.633|
               6|1b3d077a-1941-4d6e-8328-f7dc03595565|2017-12-13 13:20:24.680|
               7|c1898370-a36f-43a2-987c-0bf24fe3fb82|2017-12-13 13:20:24.727|
               8|2b50abb8-abab-412b-a4d0-4fd5ebeb5cbe|2017-12-13 13:20:24.773|
               9|5c0ab449-a087-4d8d-834f-3726061b6bfa|2017-12-13 13:20:24.803|
              10|0f3cc1d7-f484-4bde-b088-b11ef03e2f52|2017-12-13 13:20:24.850|
              11|a417a3d1-00eb-4d7f-b793-f93dc2c5391d|2017-12-13 13:20:24.900|
              12|ebd8a50f-322e-4426-a39a-566fd5535b1c|2017-12-13 13:20:24.947|
			  -- more --

Click to view Full table

businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting businessentityid values from the BusinessEntity table that do not exist in the Person table filtered by persontype using the EXCEPT operator
SELECT 
    -- Selecting the businessentityid column from the BusinessEntity table
    businessentityid   
-- Selecting data from the BusinessEntity table
FROM 
    person.businessentity    
-- Applying the EXCEPT operator to find businessentityid values from the BusinessEntity table that do not exist in the Person table
EXCEPT  
-- Selecting the businessentityid column from the Person table where persontype is 'IN'
SELECT 
    businessentityid   
-- Selecting data from the Person table, filtering by persontype 'IN'
FROM 
    person.person
WHERE 
    person.persontype = 'IN'  
-- Ordering the result set by businessentityid
ORDER BY 
    businessentityid;

Explanation:

• This SQL code retrieves businessentityid values from the BusinessEntity table that do not exist in the Person table filtered by persontype 'IN' using the EXCEPT operator.
• The first SELECT statement selects businessentityid values from the BusinessEntity table.
• The second SELECT statement selects businessentityid values from the Person table, filtering by persontype 'IN'.
• The EXCEPT operator returns only the businessentityid values from the first SELECT statement that do not exist in the result set of the second SELECT statement.
• The result set will contain businessentityid values from the BusinessEntity table that are not associated with any person of type 'IN' in the Person table.
• The result set is ordered by businessentityid.

Sample Output:

businessentityid|
----------------+
               1|
               2|
               3|
               4|
               5|
               6|
               7|
               8|
               9|
              10|
              11|
...			

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: A distinct businessentityid must be returned by both queries.
Next: Combine ProductModelID and Name columns from two tables.