How to use Intersect and Except Operators in SQL Server

SQL Server Intersect and Except Operators Definition with Examples
INTERSECT operator to return only values that match within both data sets. The data that is common to both results set; the common data is represented by the area where the two circles intersect as shown in below image.

The EXCEPT operator reruns the data that exists in one of the data set, which is not exist in the another data set which is outside the intersecting area. 
For instance, if Set A is specified to the left of the EXCEPT operator, only returns the values from Set A which are not in Set B, as shown in below image.

When you use the INTERSECT operator or EXCEPT operator, the number of columns must be the same in both queries and the columns must be in the same order. In addition, the corresponding columns between the queries must be configured with compatible data types. 

In the illustration above, that would be the data in the left circle, outside the section where the two data sets intersect. The following points sum up which operator to use to return different combinations of data:

To return the data in Set A that doesn’t overlap with B, use A EXCEPT B.
To return only the data that overlaps in the two sets, use A INTERSECT B.
To return the data in Set B that doesn’t overlap with A, use B EXCEPT A.
To return the data in all three areas without duplicates, use A UNION B.
To return the data in all three areas, including duplicates, use A UNION ALL B.
To return the data in the non-overlapping areas of both sets, use (A UNION B) EXCEPT(A INTERSECT B), or perhaps (A EXCEPT B) UNION (B EXCEPT A)

Example :
Let us suppose we have two tables with Common structure , data and unique records in each table as well as follows :
SELECT*FROM [dbo].[Tbl_Left]
SELECT*FROM [dbo].[Tbl_Right]

-----------------------------------------------------------
Now we can return the the records from Left Table(first table) which are not exist in the Right table(second table) as follows :

SELECT*FROM [dbo].[Tbl_Left]
Except
SELECT*FROM [dbo].[Tbl_Right]

-----------------------------------------------------------
Similarly, we can return the the records from Right Table(second table) which are not exist in the Left table(first table) as follows :

SELECT*FROM [dbo].[Tbl_Right]
         Except
 SELECT*FROM [dbo].[Tbl_Left]

-----------------------------------------------------------
Now we can return the Intersecting records that exist in both the Left Table(first table) and Right table(second table) as follows :

SELECT*FROM [dbo].[Tbl_Left]
INTERSECT
SELECT*FROM [dbo].[Tbl_Right]

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------