Monday, November 21, 2016

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.
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.

No comments:

Post a Comment

Popular Posts