MS SQL For Beginners

The SQL EXCEPT statement is one of the most commonly used statements to filter records when two SELECT statements are being used to select records.

The SQL EXCEPT statement returns those records from the left SELECT query, that are not present in the results returned by the SELECT query on the right side of the EXCEPT statement.

A SQL EXCEPT statement works very similarly to the way that the minus operator does in mathematics.

The EXCEPT statement was introduced in Microsoft SQL Server 2005.

In this article, you will learn how to use SQL EXCEPT with the help of some simple examples.

The conditions to execute SQL EXCEPT statement

There are a couple of conditions that have to be met before you can use the EXCEPT statement in SQL Server:

The number of columns and orders in the tables that are being used to execute the SELECT statements should be the same
The data types of the corresponding columns of both tables involved in the corresponding SELECT queries should be either the same or compatible

Creating a dummy dataset

Let’s create a dummy dataset that we will use to execute the example SQL EXCEPT statements unless you are using a fully backed up SQL database.

Run the following script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE Database BookStore;
GO
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
 )

In the script above, we create a dummy database called BookStore. The database contains two tables: Books1 and Books2.

Both tables contain 4 columns: id, name, category and price.

Now let’s insert some dummy records into both tables.

Run the following script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
USE BookStore
 
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)

The above script inserts 10 records into the Books1 table and 10 records into the Books2 table.

It is important to mention that the last 5 records in the Books1 table and the first 5 records in the Books2 table are the same.

Let’s now see how we can use SQL EXCEPT statement on this data.

Filtering records with the SQL EXCEPT statement

Let’s look at the syntax for using a SQL EXCEPT statement:

1
Right SELECT Query EXCEPT Left SELECT Query

Yes, it is that simple to execute an EXCEPT statement.

Next, we will use the SQL EXCEPT statement to select records from the Books1 table that are not present in the Books2 table. You can see that the records from ids 6 to 10 are the same in both tables.

So, if the Book1 table is on the left of the EXCEPT operator and Books2 table is on the right, the records with ids 1 to 5 will be selected from the table Books1.

To give it a try, run the following script:

1
2
3
4
USE BookStore
SELECT id, name, category, price FROM Books1
Except
SELECT id, name, category, price FROM Books2

Here is the output of the above script:

Output From Running Simple SQL Except statement - example 1

You can see that only records with ids 1 to 5 have been selected from the Books1 table since the records with ids 6 to 10 also exist in the Books2 table.

Similarly, if the Books2 table is on the left side of the SQL EXCEPT statement and the Books1 table is on the right, you will see records from the Books2 table not present in the Books1 table.

Execute the following script to see this in action:

1
2
3
4
USE BookStore
SELECT id, name, category, price FROM Books2
Except
SELECT id, name, category, price FROM Books1

Here is the output:

Output from simple SQL EXCEPT statement - Example 2

You can see that only records with ids 11 to 15 have been selected since records with ids 6-10 from the Books2 table, also exist in the Books1 table.

Using EXCEPT statements in a single table

In addition to using a SQL EXCEPT statement for filtering records from two tables, an EXCEPT statement can also be used to filter records from a single table. For example, the following EXCEPT statement will return all the records from the Books1 table where the price is less than or equal to 5000:

1
2
3
4
USE BookStore
SELECT id, name, category, price FROM Books1
Except
SELECT id, name, category, price FROM Books1 WHERE price > 5000

In the script above, we have two SELECT statements operating on a single table i.e. Books1.

The SELECT statement on the right-hand side of the EXCEPT statement selects all the records where the price is greater than 5000. The SELECT statement on the left side of the EXCEPT statement returns all the records from the Books1 table.

Next, the EXCEPT statement filters the records selected by the SELECT statement on the right, from the records returned by the SELECT statement on the left. Hence, we are only left with the records from the Books table, where the price is not greater than 5000.

Here is the output of the above query:

Output of a SQL Except statement run on a single table.

EXCEPT vs NOT NULL

Now that you know how an EXCEPT statement works, it is important to understand the difference between SQL EXCEPT statement and NOT IN statement. There are two major differences:

The EXCEPT statement only returns the distinct records, whereas a NOT IN statement returns all the records that are not filtered by the NOT IN statement
In the EXCEPT statement, the comparison between two SELECT statements is based on all the columns in both the tables. While a NOT IN statement compares values from a single column

Here is an example of how a NOT IN statement can be used to filter all records from the Books1 table, that also exist in the Books2 table:

1
2
3
USE BookStore
SELECT id, name, category, price FROM Books1
WHERE id NOT IN (SELECT id from Books2)

Output:

Output from a NOT IN statement run on a single table.

You can see that here the comparison between the first and second columns is only based on the id column.

Conclusion

The SQL EXCEPT statement is used to filter records based on the intersection of records returned via two SELECT statements. The records that are common between the two tables are filtered from the table on the left side of the SQL EXCEPT statement and the remaining records are returned.

In this article, we looked at how to use an EXCEPT statement to filter records from two tables as well as from a single table. The article also covered the difference between the EXCEPT and NOT IN statements.

Overview

One good thing to do for all of your SQL Server objects is to come up with a naming convention to use. There are not any hard and fast rules, so this is really just a guideline on what should be done.

Explanation

SQL Server uses object names and schema names to find a particular object that it needs to work with. This could be a table, stored procedure, function ,etc...

It is a good practice to come up with a standard naming convention for you objects including stored procedures.

Do not use sp_ as a prefix

One of the things you do not want to use as a standard is "sp_". This is a standard naming convention that is used in the master database. If you do not specify the database where the object is, SQL Server will first search the master database to see if the object exists there and then it will search the user database. So avoid using this as a naming convention.

Standardize on a Prefix

It is a good idea to come up with a standard prefix to use for your stored procedures. As mentioned above do not use "sp_", so here are some other options.

usp_
sp
usp
etc...

To be honest it does not really matter what you use. SQL Server will figure out that it is a stored procedure, but it is helpful to differentiate the objects, so it is easier to manage.

So a few examples could be:

spInsertPerson
uspInsertPerson
usp_InsertPerson
InsertPerson

Again this is totally up to you, but some standard is better than none.

Naming Stored Procedure Action

I liked to first give the action that the stored procedure takes and then give it a name representing the object it will affect.

So based on the actions that you may take with a stored procedure, you may use:

Insert
Delete
Update
Select
Get
Validate
etc...

So here are a few examples:

uspInsertPerson
uspGetPerson
spValidatePerson
SelectPerson
etc...

Another option is to put the object name first and the action second, this way all of the stored procedures for an object will be together.

uspPersonInsert
uspPersonDelete
uspPersonGet
etc...

Again, this does not really matter what action words that you use, but this will be helpful to classify the behavior characteristics.

Naming Stored Procedure Object

The last part of this is the object that you are working with. Some of these may be real objects like tables, but others may be business processes. Keep the names simple, but meaningful. As your database grows and you add more and more objects you will be glad that you created some standards.

So some of these may be:

uspInsertPerson - insert a new person record
uspGetAccountBalance - get the balance of an account
uspGetOrderHistory - return list of orders

Schema Names

Another thing to consider is the schema that you will use when saving the objects. A schema is the a collection of objects, so basically just a container. This is useful if you want to keep all utility like objects together or have some objects that are HR related, etc...

This logical grouping will help you differentiate the objects further and allow you to focus on a group of objects.

Here are some examples of using a schema:

HR.uspGetPerson
HR.uspInsertPerson
UTIL.uspGet
UTIL.uspGetLastBackupDate
etc...

To create a new schema you use the CREATE SCHEMA command

Here is a simple example to create a new schema called "HR" and giving authorization to this schema to "DBO".

CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]

Putting It All Together

So you basically have four parts that you should consider when you come up with a naming convention:

Schema
Prefix
Action
Object

Take the time to think through what makes the most sense and try to stick to your conventions.