SQL INTERSECT

The INTERSECT is one of the Set operators. This SQL Server Intersect returns all the common records from the left-hand side query (Left Table) and right-hand side query (Right Table). Before the SQL practical example, the image below will help you understand the Intersect.

SQL Server INTERSECT Syntax

The syntax behind the Intersect is

SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table1
INTERSECT
SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table2

The following are the set of rules for SQL intersecting:

1. First, the number of columns must be the same in all the queries.
2. The column data types should be compatible with each other.
3. Third, the order of the columns must be the same in all the queries.

For this demonstration, we will use two tables in our Database. From the below figure, you can observe that the [Employ] table has ten records

And [Employees 2016] table has four rows. There is only one distinct record (9, Rob, Verhoff), and the remaining three are the same.

SQL Server Intersect example

The following query returns all the common records in both Employ and Employees 2016 tables and displays the result set.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
  INTERSECT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2016]

SQL Server Intersect Where Clause Example

The following Query shows how to use this operator along with the WHERE Clause. In this example, we are combining two statements:

• The first SQL Server result set selects the records from Employ, whose yearly income is greater than or equal to 70000
• The second result set selects all the records from the [Employees 2016] table.
• And this operator selects all the common records in the first result set and the second result set.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
WHERE [YearlyIncome] >= 70000
 INTERSECT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2016]

Intersect Errors

The following Query will display the common errors we encountered while working with this SQL Intersect query. For this, we will use two tables (Employee and [Employees 2016]) present in our Database. The screenshot below shows that the [Employee] table has 7 columns and 14 rows.

Let us see the issues when we select all columns present in both tables. First, it means performing intersect operations on the unequal length of columns.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  INTERSECT
SELECT * FROM [Employees 2016]

Messages
--------
Msg 205, Level 16, State 1, Line 2
All queries combined using operator must have an equal number of expressions in their target lists.

I guess you can read the message. Now, let’s change the query a bit to select an equal number of columns.

SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employee]
 INTERSECT
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2016]

From the below SQL Intersect screenshot, you can see it is returning 3 records. Because there is only one distinct record (Rob, Verhoff), the remaining three records in [Employees 2016] are also present in the Employee table. And our operator selects only Common records from both tables.