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.

Set Operations

In the previous article we examined the UNION command, which allows you to combine the results of two separate queries, either as a distinct query or with the inclusion of duplicated values. The UNION command is an example of a set operation. We can illustrate the operation using the Venn diagram shown below. In this diagram, the two queries (A and B) are represented as circles. The UNION operation combines the results of both queries to give the set of values shown in the shaded area.

Venn diagram showing UNION.

In this article we will examine two other set operations provided natively by SQL Server and a third set operation that can be achieved by combining several queries. These are accessed using the EXCEPT and INTERSECT commands, which are used in the same manner as UNION. They also have similar limitations, in that the combined queries must present columns with compatible data types that appear in the same order.

Unlike the UNION command, the two new operations perform comparisons of the values in addition to returning only distinct rows. This presents an interesting situation when the individual query results include null values. In a normal query, two null values are not considered equal. However, when two nulls are compared during an EXCEPT or INTERSECT operation they are deemed to be equivalent.

JoBS Database

This article's example queries will be run against the JoBS tutorial database. The database has been produced during the course of the tutorial. If you do not have an up to date version you can create the JoBS database by downloading and executing the script at the top of this page.

Using EXCEPT

The first of the two new set operations that we will examine is EXCEPT. This command uses the results of two queries to generate a set of results. All of the rows from the first query that do not have matching rows in the second query are returned. This means that the order of the two queries is important.

The EXCEPT set operation can be represented by the Venn diagram shown below. As you can see, rows are returned from the "A" query that appears before the EXCEPT keyword. No rows from the second "B" query are returned, even if they exist within the first query's results.

Venn diagram showing EXCEPT.

To demonstrate the use of the EXCEPT command we will use the same two queries that were used in the article describing union operations. The first query retrieves the three customer complaints from the JoBS database.

SELECT
    CustomerNumber,
    Complaint,
    ComplaintTime
FROM
    CustomerComplaints

The second query returns the three rows of customer feedback. If you run both queries you will see that a matching row appears in each. This row has the feedback, "Customer does not wish to receive direct marketing information."

SELECT
    CustomerNumber,
    Message,
    FeedbackTime
FROM
    CustomerFeedback

We can use the EXCEPT operation to return every complaint that does not have a matching customer feedback row with the following query. This will return two rows:

SELECT
    CustomerNumber,
    Complaint,
    ComplaintTime
FROM
    CustomerComplaints
EXCEPT
SELECT
    CustomerNumber,
    Message,
    FeedbackTime
FROM
    CustomerFeedback

Similarly, we can retrieve all of the customer feedback messages that do not appear in the customer complaints table by reversing the two queries as follows:

SELECT
    CustomerNumber,
    Message,
    FeedbackTime
FROM
    CustomerFeedback
EXCEPT
SELECT
    CustomerNumber,
    Complaint,
    ComplaintTime
FROM
    CustomerComplaints
26 April 2009