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.