  # SQL intersect use in SQL Server

July 10, 2019

## Introduction

In this article, we will show how to use the SQL intersect logical operator using different examples.

## Requirements

1. Any SQL Server version installed. Starting in SQL Server 2000
2. The AdventureworksDW database is recommended, you can download it here. If you do not want to install it, you can use your own tables

## Getting started

The SQL intersect operator allows us to get common values between two tables or views. The following graphic shows what the intersect does. The set theory clearly explains what an intersect does. In mathematics, the intersection of A and B (A ∩ B) is the set that contains all elements of A that also belong to B.

In SQL Server, the same concept is applied (we can say that in SQL, the tables are sets and we can apply all the Set theory in tables and views).

## SQL intersect samples

OK, now that we remind the set theory and that we understand it, let’s jump to an example.

We will use the AdventureworksDW tables. We will use 2 tables. The dbo.FactInternetSales and the dbo.DimCurrency tables. We will get the common elements. Let’s take a look at the dbo.FactInternetSales first: Notice that this table has the CurrencyKey column, we will use this column to get common values between this table and the dbo.DimCurrency that contains all the CurrencyKey IDs.

Now, let’s take a look at the dbo.DimCurrency table: The currencykey is the common column between both tables, we will compare them and find the common values, the query will be this one:

The result displayed by the query is the following: These values are common in both tables. You can compare multiple columns, if applicable, it is also possible to get the intersected values between 3 or more tables. We will show these scenarios below:

## How to do a SQL intersect with 3 or more tables

The following example, will create 2 extra tables for this example:

The query is creating 2 tables named table1 and table2 based on the top 5 and top 7 rows of the dbo.FactInternetSales.

Once that we have the tables, let’s run the example:

This example will show all the common currency keys between the tables dbo.Facinternetsales, dimcurrency, table1 and table2.

## Common errors with SQL intersect

A common error with SQL intersect is the following:

Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the nvarchar value ‘yourvalue’ to data type int.

The following T-SQL code can generate the error message:

This error message means that you are trying to intersect the values of an incompatible data type. The following link will show the compatible data types in T-SQL:

Another common error message when the SQL intersect is used is the following:

Msg 205, Level 16, State 1, Line 11
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

The following query will produce the error message displayed:

The error in the example above is obvious. There is one column in the first select (currencykey) and two columns on the second select (geographykey and city).

So, the number of columns must be the same. This is obvious in this example, but in a more complex query, it will not be so obvious.

If you want to count the number of columns of a table, the following T-SQL query may be useful:

The previous example, counts the columns stored in the INFORMATION_SCHEMA.COLUMNS view of the table dimEmployee.

## Differences between SQL intersect and SQL INNER join

For some scenarios, both options can be used. The way the results is displayed are different. If you are not familiar with inner join we strongly recommend to check our link related:

The inner join will show common values between

Let’s take a look at the results of the intersect first:

The result of the previous query is the following: Now, let’s take a look at the inner join:

The result of the inner join is the following: The main visible difference is that intersect does not show repeated values. That may imply a big difference in the performance.

If we run a select distinct with the inner join, we may have the same value that we have got using the intersect clause.

In this article, we learned the SQL intersect concept. We remind the set theory to understand the SQL intersect concept and then we show examples and common errors.

SQL intersect is an option to get common values between views or tables. Finally, we compared with the inner join and found that it is different because it does not include repeated values, so it is slower because it takes more effort to remove duplicated values.

Latest posts by Daniel Calbimonte (see all)
T-SQL 