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
- How do i display the end date from StartDate in sql server?
- Including a stand-alone copy of a Database inside Published VS2010 C# Project
- Help needed in AdventureWorks in a sql query
- Join a single row in one table to n random rows in another
- SSRS 2011 SQL where with case when
- OS error 3 when backing up database
- How to remove character from an SQL query in where condition
- Query to get the accounts shows no movement in February 2016 and the balance is less than $ 2,000.00
- Bulk load data conversion error while importing DATETIME data
- debugging SSIS packages - debug.writeline
- Wide varchar field causes "Requested conversion is not supported" error using openquery with MySQL linked server
- MS Access to SQL data flow and performance
- Changing DateFormat from EU to US entityFramework
- Process for changing database passwords and avoiding website downtime
- Xampp MS SQL server PHP 5.6
- Multiple Table Joins to Improve Performance?
- pymssql connection fails with "collation not recognized"
- cakePHP validation not working at all
- SQL Server - Error converting data type varchar to bigint
- staircase behavior with pivoted data
- SQL SERVER 2012 - Getting report about columns optimisation
- persisted switching variables in long SQL script?
- OOPHP: Classes for SQL Server and MySQL
- SQL Server Bulk import data from .csv into new table
- Stored Procedure Case statement override each other
- What should the connection string be when using Azure Sql Failover groups?
- How to get Total Time in a location
- SQL server - Difference between Natively Compiled Stored Procedure and ordinary stored procedure
- t-sql counting unique words in text column
- Questions about local database vs service-based database
- Logging SQL that caused a trigger
- Using IF UPDATE on SQL Trigger when handling multiple inserted/updated records
- insert into using Values
- NServiceBus with SQL Server Message Transport
- From SQL result some vouchers not required
- Get the few letter of word in SQL
- Java connection to a SQL Server Database: Login failed for user 'sa'
- tsql to know when sql service was installed
- SQL Patindex / Regex - Match where there are 4 or less characters between 2 apostrophes
- SQL Server Function WIthin Case Statement
- How to use wildcard symbol in SQL query that is assigned as parameter value using SET?
- Truncate SQL Server transaction log file
- Microsoft-provided data provider classes, the good and the bad?
- How to insert into a temp table the info given by RESTORE FILELISTONLY / HEADERONLY / VERIFYONLY
- How to compare two xml files in mssql
- Get DDMMYYYY format in SQL Server without periods or hyphens
- Pivot, Unpivot, Cross Apply?
- What is the alternative of First for SQL Server?
- Difference between declaring variables in sql server
- Why does my defactored SQL run orders of magnitudes quicker?
- sQL: export database to asp.net
- Self Join SQL Server with aggregates
- SQL autocounter depending on a column value
- SQL Server querying xml using a cursor
- How to perform SELECT * in SQL Server to get alias with table name and field at the same time
- How to get the data from linked servers using queries
- SQL Query: DELETE all rows except for the xx most-recent
- SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified on LocalDB
- Insert multiple records
- Error 28000: Login failed for user DOMAIN\\user with pyodbc
- Issue converting JSON string into decimal
- SQL how to get latest date and update every time it occurs (not using MAX)
- apply sorting in stuff, stuff distinct and order by
- Number of times a record shows up : SQL Server
- What is the simplest way to sync a Kafka KTable to a SQL database?
- Group by two columns in SQL Server
- Validating input parameters before insert in SQL Server
- sql server management studio express 2014 not enumerating network sql servers
- Select date difference between multiple records from the same ID
- Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'month'
- Check if all ID's in a Column have a specific value in another column, different tables
- SSRS Remove Column from Report
- Table alias ignored for columns specified in INSERT statement in SQL Server
- Left outer join with subqueries with an inner join
- SQL Server Agent Job and gsutil
- SQL Server Express Stored procedure: Select, delete, insert
- Multiplicate set of dates for each ID
- Nested SQL case statement
- What's your disaster recovery plan?
- Query porformance vs performance of the same query in cursor
- Differences between Excel and SQL sorting
- Add rows in table only if those rows do not already exist in that same table
- SQL Server - Trying to de-normalize my table
- Convert 5 digit number to date
- Optimizing SQL Queries
- SQL query to select records of two tables without condition
- SQL performance: does MSSQL optimise the use of date time functions or is it better to pass it as a parameter
- Bring SQL Server database online
- SQL server ADO.NET over low bandwidth TCP/IP connection any tips/experience?
- Locking SQL Server tables or rows between methods
- When should I use SET IDENTITY_INSERT <tablename> OFF function?
- SQL Server FOR XML
- Changing a SQL Server Connection String in an ASP.NET Application
- Unable to Connect to SQL Server from Istio Envoy Proxy
- Restoring backup on sql server hosted on linux VM
- Loop through rows and add a number for a column for each of them automatically in SQL Server
- SQL Alternative to Cursor when Manipulating values of related rows
- SQL query intermediate process steps
- Is it possible to improve sort perfomance of ID-based list of rows?
- SQL Server Query for distinct rows
- SQL COUNT that includes 0 values
- Subtract one day from datetime
- Returning n columns from n to n table
- Is it possible to set a part of a select statement in a variable
- Unable to Query XML file with OPENXML in SQL
- Restore database to new location
- SQL Server 2008 DATETIME column data type does not default to current date with getdate()
- Bulk Insert with Limited Disk Space
- How to check for combination of column values in sql server table
- Better approach to write query with group by
- How to set up mailing profile on a Express Edition of SQL Server?
- SQL Server - Syntax around UNION and USE functions
- sys.objects in SQL Server shows all the objects or all object the current user can access
- log4j not initializes JDBC driver
- SQL join multiple sub-queried columns together
- SQL Pivot - adding another column
- Deal with large data set in WPF Application Using SQL Server
- How To Restore SQL Server Database using C# even if it's in use
- SQL Query - slow using replace function
- How can I format a datetime to varchar with the format dd-MMM-yyyy?
- Pulling database information from gridviews that is not displayed on the gridview
- Sql Server, Building a Non-cyclical Parent Child relationship with a Self Referencing Forgien Key
- Copy a table from an SQL Server CE 3.5 database to another in Visual Studio 2008?
- Stored procedure is giving an error
- Inserting dummy values at the end of select
- differences between ms sql microsoft's jdbc drivers and jTDS's driver
- Select distinct + select top to merge multiple rows
- How to get the last inserted value based ID descending from another table?
- Retrieving a tree of references with SQL Server
- SSRS 2008 R2, VS 2008, User Defined Table Types
- Sql Server Update Trigger within a Column
- Visual Studio 2015 Business Intelligence project templates
- SQL Query to find earliest date dependent on column value changing
- Best way to search in a varchar column in sql server
- Selecting data that could reside in many databases
- How to insert from one table into another with extra values in SQL?
- I found this script that go through the current database only and identifiy unused index but I wanna to go through all the databases
- Rolling up multiple rows into single row using 2 tables
- Most efficient way to store queries and counts of large SQL data
- Getting ranges that are not in database
- Add Missing Dates in Running Total
- SQL Server Indexed view for Full Text Search
- Are multiple nullable foreign keys a bad design?
- Restore sql server database without deleting existing database tables
- Procedure- case statement error
- How do you convert a CSV string into a table in SQL?
- Running batch with BCP commands via SQL Server Agent
- Check if exactly one variable is not null
- Query to select appropriate feedback record based on dates
- SQL Server generated "Text File" data importing in to postgressql using copy command