Sometimes, we have a scenario when we want to get a common set of rows from 2 different result sets. For example, we have 2 queries and both returns employees record. If we want to find who all employees are present in both result sets, that time we can use INTERSECT to get the result. Below is the graphical representation of how INTERSECT works.

If you see in the above graphical representation, the left 2 circles have the B and C letters in common. The left side picture shows how the 2 circles have B and C letters common which is nothing but an intersection.

Now let's see how it works in the database.

So we are going to create 2 tables, EmpTable and ManagerEmp and then we will insert records in them.
CREATE TABLE EmpTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50)) 
CREATE TABLE ManagerTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50)) 
 
INSERT INTO EmpTable 
SELECT EmpName='John',City='Stamford',Title='Operator' 
 
INSERT INTO EmpTable 
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer' 
 
INSERT INTO EmpTable 
SELECT EmpName='Smith',City='Wilton',Title='Driver' 
 
 
INSERT INTO ManagerTable 
SELECT EmpName='Mike',City='Wilton',Title='Driver' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Smith',City='Wilton',Title='Driver' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Jonathan',City='Armonk',Title='Accountant' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Warner',City='Stamford',Title='Customer Service' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer' 

Now run below the query to find out the common employees in both tables.
SELECT * FROM EmpTable 
 
SELECT * FROM ManagerTable 
 
-- INTERSECTION 
SELECT * FROM EmpTable 
INTERSECT 
SELECT * FROM ManagerTable 

Here is the output.

If you see below, in both the "EmpTable" and the "ManagerTable" tables, Luis and Smith both are employees. To join these 2 queries with INTERSECT, it gave these 2 names.

One thing is to remember here is both the tables/result sets should have the same columns and the same datatype for those columns, otherwise, it may give you "Conversion failed when converting...." if the data type does not match.

ERROR - "Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS within the up to operation."

Don’t panic if you get this error while joining your tables. there's an easy way to solve this. It happens because of the different collation settings on 2 columns we are joining.

The first step is to figure out what are the two collations that have caused the conflicts.
Let us assume that collation has not been set at the column level and instead at the db level. Then, we've to execute two straightforward statements as below.
Statements

• Select DATABASEPROPERTYYEX('DB1',N'Collation')
  
• Select DATABASEPROPERTYYEX('DB2',N'Collation') 

One more thing to make a note of here is that if you are on SharePoint, you will get an error as following.

Latin_General_CI_AS_KS_WS.
 
If you are on any other database and use the default settings, you may get this SQL_Latin_General_CP1_CI_AS.

Now, we have to do something similar to CAST, called Collate (FOR Collation).

Refer to the example below.
      select * from Demo1.dbo.Employee emp 
    join Demo2.dbo.Details dt 
    on (emp.email =dt.email COLLATE SQL_Latin_General_CP1_CI_AS) 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We know that sorting can be one of the most expensive things in an execution plan as shown below. However, we continue to do ORDER BYs repeatedly. Yes, I 100% agree that there is a need to sort a results set and that this should be done in the procedure for good reason, but my concern is having multiple sorts, erroneous sorts, and the sorts that can be done elsewhere. These are the ones that waste resources and can stifle performance.

Many of us writing procedures tend to write in code blocks. We write the SELECT, JOINS, FROMs and WHERES then immediately follow it up with and ORDER BY as a way to check result sets before moving onto the next block of code. I admit I do this almost every time. But what most developers do not do is remove unneeded ORDER BYs that are not required. This is very costly and can lead to suboptimal performance not only of your procedure but also for TEMPDB as this is where all sorting takes place.
Do you sort in your procedures that are used for data consumers like reports, ETL or an application? If you do, I ask, why are you sorting in the procedure and not in the consumer ? Many report end users will resort the data in Excel, or the report itself gives parameters for custom sorts or the data doesn’t need a sort at all. Why are you wasting resources on the SQL Server side just for it to be nullified? By removing unneeded sorts or performing the sort in the application tier you can have big performance gains. I would rather have a report, ETL process or application take the performance hit then a procedure.
 
Let’s look at one of the procedures that are available in AdventureWorks2016CPT3 called uspGetOrderTrackingBySalesOrderID. We will run it using the example execution in the code and then remove the ORDER BY, compile and rerun. We will be able to see clearly see the difference.
    USE [AdventureWorks2016CTP3] 
     
    GO 
     
    /****** Object:  StoredProcedure [dbo].[uspGetOrderTrackingBySalesOrderID]    Script Date: 1/28/2020 11:31:16 AM ******/ 
     
    SET ANSI_NULLS ON 
     
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
     
    ALTER PROCEDURE [dbo].[uspGetOrderTrackingBySalesOrderID] 
     
       @SalesOrderID [int] NULL 
    AS 
    BEGIN 
    /* Example: 
     
          exec dbo.uspGetOrderTrackingBySalesOrderID 53498 
    */ 
       SET NOCOUNT ON; 
       SET STATISTICS IO, TIME ON 
     
       SELECT 
          ot.SalesOrderID, 
          ot.CarrierTrackingNumber, 
          ot.OrderTrackingID, 
          ot.TrackingEventID, 
          te.EventName, 
          ot.EventDetails, 
          ot.EventDateTime 
       FROM 
          Sales.OrderTracking ot, 
          Sales.TrackingEvent te 
       WHERE 
          ot.SalesOrderID = @SalesOrderID AND 
          ot.TrackingEventID = te.TrackingEventID 
       --ORDER BY 
       --   ot.SalesOrderID, 
       --   ot.TrackingEventID; 
    END; 

Plan with ORDER BY

Plan without ORDER BY and Query Store graph showing the difference in duration between the two. You can clearly see the performance improvement, and this was just one sort in a very simple procedure. Take a moment and consider the sorting that happens in your code. I’d ask that when writing store procedures, doing code reviews or performance tuning that you take a second to ask why the sorts are being done in the data tier and if they can be performed elsewhere. You can see get some performance gains not only in your code but in TEMPDB as well when sorting is reigned in.