Daniel Calbimonte
insersect set theory

SQL intersect use in SQL Server

July 10, 2019 by

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.

insersect set theory

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:

Data in the factinernetsales table

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:

Data in the 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:

Results SQL intersect

Now, let’s take a look at the inner join:

The result of the inner join is the following:

Results SQL inner join

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.

Conclusion about SQL intersect

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.

Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

118 Views