SQL intersect use in SQL Server
SQL intersect use in SQL Server
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 Latest posts by Daniel Calbimonte (see all) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022
SQLShack
SQL Server training EspañolSQL intersect use in SQL Server
July 10, 2019 by Daniel CalbimonteIntroduction
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 tablesGetting 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: 123 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: 12 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: 1234567 select Currencykey from [dbo].[FactInternetSales]intersect select currencykey from DimCurrencyintersect select currencykey from dbo.table1intersect 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: 123 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: Download the compatibility chart 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: 123 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: 123 select count(*) as numColumnsfrom INFORMATION_SCHEMA.COLUMNSwhere 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: A step-by-step walkthrough of SQL Inner Join The inner join will show common values between Let’s take a look at the results of the intersect first: 123 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: 123 select f.Currencykey from [dbo].[FactInternetSales] finner join dimcurrency don 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. 123 select distinct f.Currencykey from [dbo].[FactInternetSales] finner join dimcurrency don f.currencykey=d.currencykeyConclusion 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. Author Recent Posts Daniel CalbimonteDaniel 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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte Latest posts by Daniel Calbimonte (see all) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022