Wednesday, March 14, 2018

TSQL - INTERSECT operator in SQL Server


INTERSECT operator is another most important feature in SQL Server which is used to returns distinct rows by comparing the results of two input queries. If you want to use the INTERSECT operator then both SQL queries within the INTERSECT query, the number and the order of the columns must be the same in the result sets. Apart from this, the data types must be compatible in the both queries. INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

The above illustration shows how the INTERSECT operator returns data that is common to both results set; the common data is represented by the area where the two circles intersect.
Important point to be remember
The following important points always keep in mind for INTERSECT operator -
  • The query specification or expression cannot return xml, text, ntext, image, or non-binary 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.
  • INTERSECT cannot be used in distributed partitioned view definitions, query notifications, or together with COMPUTE and COMPUTE BY clauses.
  • INTERSECT 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 INTERSECT 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 that are returned by both the query on the left and right sides of the INTERSECT operand.
----- Pull all the records
SELECT UserId,UserName,UserAge
FROM dbo.Tbl_User_Master
----  Except Operator
INTERSECT
----- 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
1
Ryan Arjun
21
3
Kimmy Wang
23
4
Rosey Gray
19

----- 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
In the above queries, you can see that we have duplicate records for User Name ‘Ryan Arjun’ but unique User Ids. We have used a derived table to get the minimum User Id to remove duplicates then used INTERSACT operator to pull the common records from both datasets.
Conclusion
INTERSECT operator is a very quick and easy way to find common results from the left and right datasets. INTERSECT 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. The one easiest ways to do that is to put our INTERSECT construction into a common table expression (CTE) and then join the expression to one of the tables to pull the additional data. 

No comments:

Post a Comment

Popular Posts