BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Except and Intersect

The twenty-sixth part of the SQL Server Programming Fundamentals tutorial reviews two set operations that are available in SQL Server 2005 queries. Both EXCEPT and INTERSECT use a combination of two sets of results to determine the rows returned.

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.

Venn diagram showing INTERSECT.

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.

Venn diagram showing reverse INTERSECT.

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