Using INTERSECT
The second set operation uses the INTERSECT command. This returns the distinct set of rows that appear in both queries, represented by the shaded area in the diagram below.
We can use INTERSECT to return all of the customer complaints in the JoBS database that also appear within the customer feedback table. In this case, the order of the two queries is unimportant. The query below will return the row that is common to both tables.
SELECT
CustomerNumber,
Complaint AS Feedback,
ComplaintTime AS Time
FROM
CustomerComplaints
INTERSECT
SELECT
CustomerNumber,
Message,
FeedbackTime
FROM
CustomerFeedback
Combining UNION and EXCEPT
The final set operation that we will consider is not available in SQL Server using a single command. However, it is described here for completeness. The Venn diagram below shows the set that we will obtain. It is the set of distinct rows that appear in either of the two combined queries but not both.
To obtain the desired results we will perform both of the INTERSECT queries that we executed earlier in the article. We can then combine the results using a UNION ALL command. It is important to use parentheses around the two INTERSECT queries to indicate to SQL Server that these should be performed before the final combination of the results. The command to obtain the set is therefore as follows:
(SELECT
CustomerNumber,
Complaint AS Feedback,
ComplaintTime AS Time
FROM
CustomerComplaints
EXCEPT
SELECT
CustomerNumber,
Message,
FeedbackTime
FROM
CustomerFeedback)
UNION ALL
(SELECT
CustomerNumber,
Message,
FeedbackTime
FROM
CustomerFeedback
EXCEPT
SELECT
CustomerNumber,
Complaint AS Feedback,
ComplaintTime AS Time
FROM
CustomerComplaints)
When you run the above query you should see the four rows that appear in either the customer complaints or customer feedback table but not in both.
26 April 2009