In this post, we will discuss the SQL Server EXCEPT Operator with a few examples. So, what does the EXCEPT operator do? The EXCEPT operator returns unique rows from the left query that are not in the right query results.
Let’s understand this with an example. We’ll be using these two tables, TblCustomerSource and TblCustomerTarget.
I’ve already created these tables, and here is the SQL script to create them and populate them with test data.
/****** Object: Table [dbo].[TblCustomerSource] Script Date: 12/07/2020 6:42:06 PM ******/ CREATE TABLE [dbo].[TblCustomerSource]( [Id] [int] NOT NULL, [CustomerName] [nvarchar](50) NOT NULL, [Gender] [nvarchar](50) NULL, ) GO /****** Object: Table [dbo].[TblCustomerTarget] Script Date: 12/07/2020 6:42:06 PM ******/ CREATE TABLE [dbo].[TblCustomerTarget]( [Id] [int] NOT NULL, [CustomerName] [nvarchar](50) NULL, [Gender] [nvarchar](50) NULL, ) GO INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (3, N'Colton', N'Male') GO INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (4, N'Jaxson', N'Male') GO INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (5, N'Angel', N'Female') GO INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (6, N'Brayden', N'Male') GO INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (1, N'Dr. Jacob', N'Male') GO INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (2, N'Johnson', N'Male') GO INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (3, N'Colton', N'Male') GO INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (4, N'Jaxson', N'Male') GO
Now, let's write a query that involves both of these tables and the EXCEPT operator. When we execute the query below, what result do we expect?
Select Id, CustomerName, Gender From TblCustomerTarget Except Select Id, CustomerName, Gender From TblCustomerSource
What is this except operator going to do?
It's going to return the rows from the left query that are not present in the right query results. If we examine these two tables, records for employees Colton and Jaxson, we'll notice that these two records are present in both tables. So when we execute the above EXCEPT query, it should only return the first two rows.
This operator was introduced in SQL Server 2005. The most important thing to keep in mind is that for the EXCEPT operator to work, the number and order of the columns must be the same in both queries, and the data types must also be either the same or compatible.
What’s going to happen if the number and the order of the columns are not the same?
For example, let’s remove the gender column from the first query. So in this query, we only have two columns, whereas in the second query we have three columns. If I execute the query, we get an error.
Now, in this example, we have returned the results that are present in Table A but not in Table B. Now, what if you want it the other way around?
I want the rows that are present in Table B, but not in Table A. In that case, you can reverse the queries.
Select Id, CustomerName, Gender From TblCustomerSource Except Select Id, CustomerName, Gender From TblCustomerTarget
So when we execute the query, we only get the rows that are present in Table B but not in Table A.
Now, here we have seen how to use the EXCEPT operator on two different tables. Is it possible to use it on a single table? Absolutely. You can use it on a single table, and you can even have a WHERE clause.
For the single table example, we will use table TblProfessor. Again, I have already created it and populated it with test data. Here is the SQL script to do that.
GO CREATE TABLE [dbo].[TblProfessor]( [Id] [int] IDENTITY(1,1) NOT NULL, [EmployeeName] [nvarchar](max) NULL, [EmployeeSalary] [float] NULL, [Country] [nvarchar](max) NULL, [Adress] [nvarchar](max) NULL, [DepartmentId] [int] NULL, CONSTRAINT [PK_TblEmployeeSalary] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[TblProfessor] ON GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (1, N'Hunter', 80000, N'UK', N'Osaka', 1) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (2, N'Connor', 70000, N'India', N'Kyoto', 1) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (3, N'Ezra', 60000, N'USA', N'Vienna, Austria', 2) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (4, N'Aaron', 55000, N'USA', N'Tokyo', 3) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (5, N'Adrian', 85000, N'India', N'Amsterdam, Netherlands', 3) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (6, N'Easton', 72000, N'India', N'Barcelona, Spain', 1) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (7, N'Colton', 85000, N'Australia', N'Singapore', 1) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (8, N'Angel', 42000, N'Australia', N'Monte Carlo, Monaco', 3) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (9, N'Jaxson', 32000, N'USA', N'Cologne, Germany', 2) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (10, N'Greyson', 45000, N'Australia', N'Amsterdam, Netherlands', 1) GO INSERT [dbo].[TblProfessor] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (11, N'Brayden', 78000, N'UK', N'Tokyo', 1) GO SET IDENTITY_INSERT [dbo].[TblProfessor] OFF GO
So when we execute the below query, select * from [dbo].[TblProfessor], it’s going to give us all the employee rows.
Right now, what I want to do is add a WHERE clause: where salary is greater than or equal to 40000.
select * from [dbo].[TblProfessor] Where EmployeeSalary >= 40000
So, what is this going to do? It’s going to give us all the professors who have a salary greater than or equal to 40000. Now, I’m going to use the EXCEPT operator.
Select Id, EmployeeName, Adress, EmployeeSalary From [dbo].[TblProfessor] Where EmployeeSalary >= 50000 Except Select Id, EmployeeName, Adress, EmployeeSalary From [dbo].[TblProfessor] Where EmployeeSalary >= 70000 order By EmployeeName
This query selects the Id, EmployeeName, Address, and EmployeeSalary columns from the TblProfessor table. It retrieves records where the EmployeeSalary is greater than or equal to 50000.
Then, it uses the EXCEPT operator to remove records where the EmployeeSalary is greater than or equal to 70000.Finally, it orders the resulting records by the EmployeeName.
So, the output of this query would be a list of professors with salaries between 50000 and 70000, ordered by their names.
When we execute the above query, we should only get those professors whose salary is between 40000 and 70000. So we’re using it on the same table.
Now, we can also use the ORDER BY clause, but you have to use it after the right query, that is, after the second query. You can’t use ORDER BY in the first query when using EXCEPT.
