How to Usr EXCEPT Operator with an example in Sql

How to Use EXCEPT Operator with an example in Sql

In this post will discuss the SQL server EXCEPT Operator with a few examples. So what does except operator do? 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.
1

/****** 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’s going to involve both these tables and the EXCEPT operator.when we execute the below query, what is the result that 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 as the rows from the left query that are not there in the right query results. And if we look at these two tables, records, that is employee Colton and Jaxson, those two records are present in both the tables. So when we execute above except query, it should only return as the first two rows.

2

This operator is introduced in SQL Server 2005. The most important thing to keep in mind is that to work the EXCEPT operator ,number and the order of the columns must be the same in both the queries and the data types must also be either 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 first query. So in this query, we only have two columns, whereas in the second query we have got three columns and if I execute the query we get an error.

2

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 the other way around?

I want the rows that are present in table B, but not in table A. If that’s the case, you can reverse queries.

Select Id, CustomerName, Gender
From TblCustomerSource
Except
Select Id, CustomerName, Gender
From TblCustomerTarget

3

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 where clause.

So for the single table example will use table TblProfessor, again I have already created that in populated it with test data. And here is the SQL script to do that

3

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 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 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

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 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.

4

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.