Should I Intersect or Except?

Two very useful functions were introduced, in SQL Server 2005, Intersect and Except.  These two functions can greatly limit the amount of coding required to find the differences or similarities between two result sets.  Let’s start with the Except operator.  Except returns the distinct values from the left query that do not exist in the right query.  When I say left and right query I am referring to the position of the query in relation to EXCEPT.  Take a look at the example below.  In this example, the table 1 query is to the left and table 2 is to the right.  This means the result will include all the rows from table1 than do not exist in table2.

SELECT * FROM Table1 EXCEPT SELECT * FROM Table2

You are probably thinking okay…, but how can this help me?  I believe the best way to demonstrate the behavior is through example.  Let’s start by creating test tables.

DECLARE @t1 TABLE(

id INT,

col CHAR(1)

)

INSERT INTO @t1 VALUES (1,'a');

INSERT INTO @t1 VALUES (2,'b');

INSERT INTO @t1 VALUES (3,'c');

INSERT INTO @t1 VALUES (4,'d');

DECLARE @t2 TABLE(

id INT,

col CHAR(1)

)

INSERT INTO @t2 VALUES (1,'a');

INSERT INTO @t2 VALUES (2,'b');

INSERT INTO @t2 VALUES (4,'d');

INSERT INTO @t2 VALUES (5,'e');

Now, we can test.  In this example we will use the same logic that we presented earlier. 

--use EXCEPT

SELECT 

    *

FROM @t1

EXCEPT

SELECT *

FROM @t2

The results are as follows:

The results is only id =3, col=’c’ because this row is the only row that existed in @t1 that did not exist in @t2.  This example may not seem like it saves you a lot of coding,  but imagine how many statements you would need to check each column, especially when the table has lots of columns. Now let’s expand the example to show distinct values, in both tables.  So how do we get the distinct rows, for each table?  If you said, use two except operators, you are correct.  We can simply reverse the left and right queries to change the logic such that we get the distinct rows from @t2.  We will use the Union all operator to append the results together, so that we get one final result set of rows and the table they exist in.

--use EXCEPT

SELECT *

FROM(

    SELECT 

        't1' AS tbl,id,col

    FROM @t1

    EXCEPT --orginal except

    SELECT 

        't1' AS tbl,id,col

    FROM @t2

    UNION ALL

    SELECT 

        't2' AS tbl,id,col

    FROM @t2

    EXCEPT –reverse the except logic

    SELECT 

        't2' AS tbl,id,col

    FROM @t1

) AS a    

Results:

Now lets move on to the Intersect operator.  Intersect does what the name implies. Intersect returns all the rows where the query results match between the left and right queries.

Let’s see it in action:

--use intersect

SELECT 

    *

FROM @t1

INTERSECT

SELECT *

FROM @t2

The rows returned show all the values that exist in both query result sets.  This is a very quick and easy way to get like values.  The alternative to using the intersect method is to use a predicate that contains every column in the table, as shown below.

--use join

SELECT 

    t1.*

FROM @t1 t1

INNER JOIN @t2 t2

    ON  t1.id = t2.id 

    AND t1.col = t2.col

You may be thinking that this does not look so bad, which I can agree with; however, what happens when your queries has 10 or more columns?  Can you imagine having to scale this code to work with 20 filters.  I just think it is a lot easier to use intersect for this, oppose to hand coding the predicate. One could argue that checksum is a valid alternative.  Checksum can be a good alternative; however, it prevents an index from being used, so I would  be a little apprehensive to use it, especially if you are implementing the solution in a production environment.

As you can see, these new function removes a lot of  coding complexity.  The performance of each of these operators varies depending on the size and complexity of the results returned by each query and the indexing in place.  I typically use these operators to quickly identify RI violators and to quickly identify data that exists in one table and not another.  I hope that this tip saves you time and that you learned something new. 

Happy coding!