Module 3: INTERSECT and EXCEPT

Previous Tutorial - Next Tutorial

The INTERSECT operator is the third type of SET operators. The intersect operator retrieves the common unique rows from both the first and the second query. In other words, if a row appears at least once in the first result set and at least once in the second result set, then it appears one in the main results set. In short, it keeps the rows that are common to all the queries. But here notice the duplicate rows are removed. This is the general syntax for the Intersect. It is the same as the union and union all of which we have already learned in the previous slide.

Now in the diagram, this operator keeps the rows that are common to all the queries. Query one contains A, B, and C rows, while query to contain B, C, and D rows. when we perform the intersect operation between these two queries, then the result will be of two rows B and C, which are common in both the tables. Let’s open the SQL server management studio to see this operator in action.

Now to easily understand the intersect operator here, I have written the two simple queries using UNION. If I execute these two queries, then it will return two result sets. The first results set contains A, B, and C rows and the second result set contains B, C, and D rows. If I join these two queries using intersect operator, then it will return me the rows which are common in both the queries. The common rows are B and C.

Intersect Tutorial

Combine these two queries using intersect operator. If I execute this query then it will return the two rows which are common in both queries. In the same way, I can use the normal table that exists in this adventure work database. The currency table contains 105 rows and the country region table contains 109 rows.

Now, if I use the intersect operator and execute this query then It will return the 97 rows. I have simply connected the two queries with the intersect operator, now it’s on in the result. It returns 97. It means it returns only those rows, which appears at least one in the country region table and at least one in the currency table. In short, it returns to those rules which are common in both tables.

Let’s understand the next operator, which is the EXCEPT operator. The EXCEPT operator is the last type in the SET operators. This operator will return the unique or distinct rows from the first query that are not present in the second query result. In other words, if a row appears at least one in the first query result and zero times in the second query result, then it will return once in the main output. In short, this operator keeps the rows from query one which is not included in query two. This is the general syntax of the except operator. It is the same as the previous one. Just replace the INTERSECT with the EXCEPT.

In the diagram, query one containing A, B, and C rows while query two contain C, B, and E rows. When we perform the EXCEPT operation between these two queries, then the result will be of two rows A and B, which are from the first query and it will not return C in the result because it is also there in query two. So as I said earlier, that this operator keeps the rows from query one which is not included in query two.

Intersect Explained

Let’s not get much into theory and open the management studio to see this operator in action. To understand the EXCEPT operator again, we will use this simple query. Here I have defined the two simple queries using UNION. If I execute this query, then it will return two result sets. The first result set contains three rows, A, B, and C, the second result set contains three rows C, D, and E. When I combine this query using EXCEPT operator and execute the query then it will return A and B which are from the first query, here C is not returning because it is present in the second query. In short, EXCEPT operator will return unique and distinct rows only from the first query that are not present in the second query.

In the same way, you can use the normal table which is present in this adventure work database. Here again, we will use the currency and country region table. If I execute the first query, then it will contain 105 rows and if I execute the second query, then it will contain 109 rows. When I combine these two queries using EXCEPT operator and execute this query, then it will return 8 rows. In the result set, it will give me the currency code from the currency table, which is not present in the country region table.

This SELECT statement is nearly identical to the Intersect. But the important point to remember when using the EXCEPT operator is it returns data only from the first query. The final result set will include data only from the currency table and not from the country region table. That’s it from these set operators. In the next slide, we will learn about the type of subqueries.

Some conditions must be satisfied before users can apply or use the EXCEPT in SQL Server. These conditions are as follows:

Always remember that the total number of columns and sequences in the tables that are being utilized to perform the SELECT clause should be identical.
Users should also remember that the data types of the identical columns of both tables included in the SELECT queries should be both identical or congenial.

Users should also remember that EXCEPT gives different rows from the left query. On the other hand, the INTERSECT gives different rows that are returned by both the left and right inserted queries. It is also important to remember that when an EXCEPT operation is shown by applying the Showplan feature in the Management Studio, the process looks like a left join, and an INTERSECT action looks like a left semi-join.

The EXCEPT statement only gives the different data, while a NOT IN statement gives all the data that is not separated by the NOT IN clause.

Also, the correlation between two SELECT clauses is dependent on all the columns. Whereas a NOT IN clause matches data from a particular column.

The following query is displaying any different values that are given by both the query of INTERSECT:

-- Uses MyDatabase

SELECT ObjectID  
FROM Trade.Object 
INTERSECT 
SELECT ObjectID  
FROM Trade.Order ;

On the other hand, the following query gives any different values from the query left of the EXCEPT statement:

-- Uses MyDatabase

SELECT ObjectID  
FROM Trade.Object 
EXCEPT 
SELECT ObjectID  
FROM Trade.Order ;

Web API for developers Free Trial Offline SDK

Here’s SQL video tutorial:

Other useful tutorials: