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.
/****** 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.
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.
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
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
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.
The post How to Use EXCEPT Operator with an example in Sql appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?