TSQL - Except Operator in SQL Server

Except operator is another most important feature in SQL Server which is used to returns distinct rows by comparing the results of two input queries. Both SQL queries within the EXCEPT query, the number and the order of the columns must be the same in the result sets within similar data types. EXCEPT operator always returns distinct rows from the left input query and these are not output by the right input query. As we know that MINUS is not available in SQL Server, but EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle.

Important point to be remember

There are few important points always keep in mind for Except operator which are given below:

1. The query specification or expression cannot return xml, text, ntext, image, or nonbinary CLR user-defined type columns because these data types are not comparable.
2. Two NULL values are considered equal when comparing column values for determining DISTINCT rows.
3. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
4. EXCEPT operator cannot be used in distributed partitioned view definitions, query notifications.
5. EXCEPT operator  may be used in distributed queries, but are only executed on the local server and not pushed to the linked server because these queries may affect performance.

To understand the except operator functionality, we are going to create an example of User Master Table as given below:

---- Create table User Master table CREATE TABLE dbo.Tbl_User_Master ( UserId int identity(1,1) primary key, UserName varchar(20), UserAge Int ) ---- Insert records into User Master table INSERT INTO dbo.Tbl_User_Master (UserName, UserAge) Values('Ryan Arjun',21), ('Ryan Arjun',21), ('Kimmy Wang',23), ('Rosey Gray',19) ----- Pull the records select UserId,UserName,UserAge   from dbo.Tbl_User_Master UserId UserName UserAge 1 Ryan Arjun 21 2 Ryan Arjun 21 3 Kimmy Wang 23 4 Rosey Gray 19

The following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query.

----- Pull all the records select UserId,UserName,UserAge from dbo.Tbl_User_Master ----  Except Operator EXCEPT ----- Pull the unique records from derived table query select UserId,UserName,UserAge FROM ( ---- Pull the unique records select UserId=Min(UserId), UserName, UserAge FROM dbo.Tbl_User_Master GROUP BY UserName, UserAge )UniqueRecords UserId UserName UserAge 2 Ryan Arjun 21 ----- Output of the derived table query select UserId,UserName,UserAge FROM ( ---- Pull the unique records select UserId=Min(UserId), UserName, UserAge FROM dbo.Tbl_User_Master GROUP BY UserName, UserAge )UniqueRecords UserId UserName UserAge 1 Ryan Arjun 21 3 Kimmy Wang 23 4 Rosey Gray 19

Conclusion

Except operator is a very quick and easy way to find differences, especially when needing to get all differences including null. EXCEPT is good when we don't know or don't have time to fine-tuned the query performance and easily available for on the fly coding.