SQL INTERSECT

The SQL Server INTERSECT is one of the Set operators that returns all the common records from the left-hand side query (Left Table) and right-hand side query (Right Table). This article will show you how to use the SQL INTERSECT in select statements and its use case scenarios.

The SQL INTERSECT operator allows you to find common rows across tables. In real time, you can use this operator to combine the sales data across different platforms or regions to find the purchase patterns. 

When there are two tables, we can say left and right. Generally, the SQL Server INTERSECT operator combines the rows from two or more SELECT statements, removes uncommon or unique records, and returns the matching rows. The best part is that it removes the duplicate rows.

SQL INTERSECT

The following is the set of rules for the SQL intersect operator:

  1. First, the number of columns must be the same in all the Select statements.
  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 Select statements.

Before the SQL practical example, the image below will help you understand the Except and Intersect operators.

SQL Except and INTERSECT Ven Diagram 2

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

As you can see from the syntax definition itself, the intersect operator performs row by row and looks for the available rows in all the select statements. The following image shows the diagrammatic representation of the intersect.

VEN Diagram 1

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

First Table Data

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

Second Table Data 2

SQL Server Intersect example

SQL INTERSECT is commonly used to retrieve common records between two tables. 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 INTERSECT set operator Distinct records Example 3

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]
SQL INTERSECT Where Clause Example 4

Intersect Errors – Unequal columns and Data types

The following Query will display your common errors 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.

Employee Table 12

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.

SQL INTERSECT 6
Categories SQL