AdventureWorks Database: Any distinct businessentityid from the first query not found in the second query
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.
Sample table: Person.BusinessEntity
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 --
Sample table: Person.Person
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.