Introduction
In this article, we will show how to use the SQL intersect logical operator using different examples.
Requirements
- Any SQL Server version installed. Starting in SQL Server 2000
- 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:
|
1 2 3 |
select Currencykey from [dbo].[FactInternetSales] intersect select currencykey from DimCurrency |
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:
|
1 2 |
select top 5 * into dbo.table1 from [dbo].[FactInternetSales] select top 7 * into dbo.table2 from [dbo].[FactInternetSales] |
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:
|
1 2 3 4 5 6 7 |
select Currencykey from [dbo].[FactInternetSales] intersect select currencykey from DimCurrency intersect select currencykey from dbo.table1 intersect select currencykey from dbo.table2 |
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:
|
1 2 3 |
select Currencykey from [dbo].[FactInternetSales] intersect select EnglishCountryRegionName from [dbo].[DimGeography] |
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:
|
1 2 3 |
select Currencykey from [dbo].[FactInternetSales] intersect select Geographykey,city from [dbo].[DimGeography] |
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:
|
1 2 3 |
select count(*) as numColumns from INFORMATION_SCHEMA.COLUMNS where table_name = 'DimEmployee' |
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:
|
1 2 3 |
select Currencykey from [dbo].[FactInternetSales] intersect select currencykey from DimCurrency |
The result of the previous query is the following:
Now, let’s take a look at the inner join:
|
1 2 3 |
select f.Currencykey from [dbo].[FactInternetSales] f inner join dimcurrency d on f.currencykey=d.currencykey |
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.
|
1 2 3 |
select distinct f.Currencykey from [dbo].[FactInternetSales] f inner join dimcurrency d on f.currencykey=d.currencykey |
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.
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023