The SQL EXCEPT statement is a powerful tool for filtering records when working with multiple SELECT statements. It allows you to retrieve records from the left SELECT query that are not present in the results of the right SELECT query. Similar to the minus operator in mathematics, the EXCEPT statement helps you find the differences between two sets of data.

Before using the EXCEPT statement in SQL Server, there are a few conditions that need to be met:

• The number of columns and their order in the tables used in the SELECT statements should be the same.
• The data types of the corresponding columns in both tables should be either the same or compatible.

Let’s start by creating a dummy dataset to demonstrate the usage of the EXCEPT statement:

CREATE DATABASE BookStore;

USE BookStore;

CREATE TABLE Books1 (
  id INT,
  name VARCHAR(50) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price INT NOT NULL
);

CREATE TABLE Books2 (
  id INT,
  name VARCHAR(50) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price INT NOT NULL
);

INSERT INTO Books1 VALUES
  (1, 'Book1', 'Cat1', 1800),
  (2, 'Book2', 'Cat2', 1500),
  (3, 'Book3', 'Cat3', 2000),
  (4, 'Book4', 'Cat4', 1300),
  (5, 'Book5', 'Cat5', 1500),
  (6, 'Book6', 'Cat6', 5000),
  (7, 'Book7', 'Cat7', 8000),
  (8, 'Book8', 'Cat8', 5000),
  (9, 'Book9', 'Cat9', 5400),
  (10, 'Book10', 'Cat10', 3200);

INSERT INTO Books2 VALUES
  (6, 'Book6', 'Cat6', 5000),
  (7, 'Book7', 'Cat7', 8000),
  (8, 'Book8', 'Cat8', 5000),
  (9, 'Book9', 'Cat9', 5400),
  (10, 'Book10', 'Cat10', 3200),
  (11, 'Book11', 'Cat11', 5000),
  (12, 'Book12', 'Cat12', 8000),
  (13, 'Book13', 'Cat13', 5000),
  (14, 'Book14', 'Cat14', 5400),
  (15, 'Book15', 'Cat15', 3200);

In the above example, we have created a database called “BookStore” and two tables, “Books1” and “Books2”. Both tables have the same structure with columns for id, name, category, and price. We have inserted some dummy records into both tables.

Now, let’s see how we can use the SQL EXCEPT statement to filter records:

USE BookStore;

SELECT id, name, category, price
FROM Books1
EXCEPT
SELECT id, name, category, price
FROM Books2;

The above query will return the records from the “Books1” table that are not present in the “Books2” table. In this case, the records with ids 1 to 5 will be selected from the “Books1” table.

Similarly, if we reverse the order of the tables in the EXCEPT statement, we can retrieve the records from the “Books2” table that are not present in the “Books1” table:

USE BookStore;

SELECT id, name, category, price
FROM Books2
EXCEPT
SELECT id, name, category, price
FROM Books1;

In this case, the query will return the records with ids 11 to 15 from the “Books2” table.

Aside from filtering records from two tables, the EXCEPT statement can also be used to filter records from a single table. For example, the following query will return all the records from the “Books1” table where the price is less than or equal to 5000:

USE BookStore;

SELECT id, name, category, price
FROM Books1
EXCEPT
SELECT id, name, category, price
FROM Books1
WHERE price > 5000;

In this case, the EXCEPT statement filters out the records selected by the second SELECT statement, which retrieves records where the price is greater than 5000. The result is the records from the “Books1” table where the price is not greater than 5000.

It’s important to note that the EXCEPT statement only returns distinct records, while the NOT IN statement returns all the records that are not filtered by the NOT IN condition. Additionally, the EXCEPT statement compares all columns in both tables, whereas the NOT IN statement compares values from a single column.

In conclusion, the SQL EXCEPT statement is a useful tool for filtering records based on the differences between two sets of data. Whether you need to filter records from two tables or a single table, the EXCEPT statement can help you achieve your desired results.