Intersect and Except in SQL Server 2005

The Intersect and Except statements were introduced in SQL Server 2005. It simplifies queries between tables to fetch the common records or to retrieve the exceptions of common records in a table.

The above figure explains the functionality of these two statements using the mathematical representation. The two circles have joined each other and both tables have some common area. The common area between each of the two circle areas is called the intersect.

The Except in the other image shows two circles which have placed one another. There are three parts. One is the common area between the two circles and two other are the uncommon spaces of circle1 and circle2. The except functionality here describes the uncommon space of one circle.

Let's see these concepts in the SQL Server with an example.

There are two SQL Server tables available. One is ProductInStock which is used to keep the stock quantity of the product.

Another table is named ProductPurchaseOrder, which is used to store the purchase product order details table.

Intersect

The intersect statement is used to fetch the common records between the tables. Here we have two tables. Both tables have similar kinds of records.

The above result shows there are two common records available in the two tables. Here we have not taken the identity columns because it may be different for the same record between the tables.

Except

The Except table is used to query the results of the table1 except the common records of table2 are called Except statements.

The above result shows both tables have some common records like HP and HCL except Sony. The left-hand side table will return the exception records of the right-hand side table.

Let's change the order of the tables for the preceding example.

Here the exception record between these two tables is "Seagate"; it is the exception item in the product purchase order table.