TSQL - Except Operator in TSQL

Except returns distinct records from top result set which are not present in second result set. To apply Except the number of columns has to be same and data type of columns for both results sets should match.

Except is very helpful for quick analysis. By changing the position of Queries (top or bottom) we can get the records which are not present in each other.

Let's see Except in action!
Create two Temp Tables with some data as given below

SELECT * INTO #T_Customer1

FROM (SELECT N'1' AS [Id], N'Aamir' AS [FName], N'Shahzad' AS [LName] UNION ALL

SELECT N'2' AS [Id], N'Robert' AS [FName], N'Ladson' AS [LName] UNION ALL

SELECT N'3' AS [Id], N'John' AS [FName], N'Rivers' AS [LName] UNION ALL

SELECT N'2' AS [Id], N'Robert' AS [FName], N'Ladson' AS [LName] ) t;

SELECT * INTO #T_Customer2

FROM (SELECT N'1' AS [Id], N'Aamir' AS [FName], N'Shahzad' AS [LName] UNION ALL

SELECT N'2' AS [Id], N'Robert' AS [FName], N'Jason' AS [LName] UNION ALL

SELECT N'3' AS [Id], N'John' AS [FName], N'Rivers' AS [LName] ) t;

Now we want to get all the records from #T_Customer1 which do not exist in table #T_Customer2. Use the below query with Except.

SELECT Id,FName,LName FROM #T_Customer1

EXCEPT
SELECT ID,FName,LName FROM #T_Customer2

Fig 1: Using Except Operator in TSQL

As we can see that the distinct record/s is returned by query which exists in first result set but does not exists in second result set.

If we want to get all the records from first result set regardless duplicate or not, we can use WHERE NOT EXISTS clause as shown below

SELECT Id,FName,LName FROM #T_Customer1 O

WHERE NOT EXISTS (

SELECT 1 FROM #T_Customer2 I 

WHERE O.Id=I.Id 

AND O.FName=I.FName 

AND O.LName=I.LName)

The above query is going to return us 2 records as Robert Ladson exists in first table two times.

Fig 2: Use Where Not Exists to find out Non matching Records

Watch the Video Demo " How Except Operator works in SQL Server"