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:
- 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.
- Two NULL values are considered equal when comparing column values for determining DISTINCT rows.
- Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
- EXCEPT operator cannot be used in distributed partitioned view definitions, query notifications.
- 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
|
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
----- 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
|
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.