SQL INTERSECT

The SQL INTERSECT is one of the Set operator provided by the SQL Server. 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 INTERSECT 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 Intersect 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

SQL INTERSECT 10

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

SQL UNION Example 2

SQL Intersect example

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

-- SQL Server Intersect Example
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employ]
  INTERSECT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employees 2016]
SQL INTERSECT 3

SQL Intersect Where Clause example

The following Query shows how to use the Intersect operator along with the WHERE Clause. In this Intersect 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 the intersect operator selects all the records that are common in the first result set, and the second result set.
-- SQL Server Intersect Example
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employ]
WHERE [YearlyIncome] >= 70000
 INTERSECT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employees 2016]
SQL INTERSECT 4

SQL Intersect Errors

The following Query will display the common errors that we encounter while working with intersect query. For this, We are going to use two tables (Employee, and [Employees 2016]) present in our [SQL Tutorial] 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.

-- SQL Server Intersect Example
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [SQL Tutorial].[dbo].[Employee]
  INTERSECT
SELECT * FROM [SQL Tutorial].[dbo].[Employees 2016]
Messages
--------
Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or 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

-- SQL Server Intersect Example
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employee]
 INTERSECT
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[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 Intersect operator selects only Common records from both the tables.

SQL INTERSECT 6

Comments are closed.