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"