Explore SQL EXCEPT statement

The SQL EXCEPT statement is helpful for filtering records between the two select statements. Let's say you have two select statements as below.

SELECT 1 EXCEPT SELECT 2

The SQL EXCEPT function returns the rows from the left side select statement, which are not present in the select statement present on the right side of the SQL EXCEPT statement. It is similar to the MINUS statement of mathematics.

Conditions for EXCEPT statement:

The select statement should have the same number of columns and orders.

Both select statements (tables) columns should have the same or compatible data types.

Let's create a sample dataset using the following query.

Create Table Products1
(
   ID INT,
   Name varchar(20),
   ProductCategory varchar(10),
   IsAvailable bit
)
Create Table Products2
(
   ID INT,
   Name varchar(20),
   ProductCategory varchar(10),
   IsAvailable bit
)
Insert into Products1 values
       (1,'ProductA','Category1',1),
       (2,'ProductB','Category2',1),
       (3,'ProductC','Category1',0),
       (4,'ProductE','Category2',0),
       (5,'ProductF','Category3',1),
       (6,'ProductG','Category4',1)
Insert into Products2 VALUES
       (3,'ProductC','Category1',0),
       (4,'ProductE','Category2',0)

The Product1 and Product2 tables have the following data:

We used the SQL EXCEPT function between select statements on the [Product1] and [Product2] tables. Both select statements have the same columns [ID],[Name],[ProductCategory], and [IsAvailable].

SELECT  [ID]
     ,[Name]
     ,[ProductCategory]
     ,[IsAvailable]
 FROM [DemoDatabase].[dbo].[Products1]
EXCEPT
SELECT  [ID]
     ,[Name]
     ,[ProductCategory]
     ,[IsAvailable]
 FROM [DemoDatabase].[dbo].[Products2]

The EXCEPT function output records from the left table (Product1) that are not present in the right side table (product2)

If we reverse the select statement orders, the query output changes. This time the query returns rows from the Product2 table which are not present in the product1 table.

SELECT  [ID]
     ,[Name]
     ,[ProductCategory]
     ,[IsAvailable]
 FROM [DemoDatabase].[dbo].[Products2]
EXCEPT
SELECT  [ID]
     ,[Name]
     ,[ProductCategory]
     ,[IsAvailable]
 FROM [DemoDatabase].[dbo].[Products1]

In my demo data, we do not have any data in the Product2 table that is not present in the Product1 table; therefore, the output is blank.

Let's insert a new record in the Product2 table for ID 7. This record is not available in the product1 table. Therefore, the Except function returns the following data.

Insert into Products2 VALUES
              (7,'ProductH','Category1',1)

As stated earlier, the number of columns and their data types should be the same in both select statements. If the number of columns differs, you get the following message: