SQL INTERSECT

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

SQL Except and INTERSECT Ven Diagram 2

SQL INTERSECT Syntax

The syntax behind the SQL Server 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 intersecting in SQL Server:

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

For this demonstration, We are going to use two tables present in our Database. From the below figure you can observe that, [Employ] table have ten records

First Table Data

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

Second Table Data 2

SQL Intersect example

The following query returns all the records that are common in both Employ, Employees 2016 table, and display the result set.

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

SQL 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 [Employees 2016] table.
  • And this operator selects all the records that are common 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]
SQL INTERSECT 4

Intersect Errors

The following Query will display the common errors that we encounter while working with this SQL Intersect query. For this, We are going to use two tables (Employee, and [Employees 2016]) present in our Database. From the below screenshot, you can see that the [Employee] table has 7 columns and 14 rows.

SQL INTERSECT 12

Let us see the issues when we select all columns present in both the tables. It means performing SQL Server intersect operation 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 a UNION, INTERSECTor EXCEPT 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 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 screenshot, you can see it is returning 3 records. Because there is only one distinct record (Rob, Verhoff), and the remaining three records in [Employees 2016] are also present in the Employee table. And our operator selects only Common records from both the tables.

SQL INTERSECT 6

Comments are closed.