SQL在SQL Server中相交使用

介绍 (Introduction)

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

在本文中，我们将通过不同的示例展示如何使用SQL相交逻辑运算符。

要求 (Requirements)

1. Any SQL Server version installed. Starting in SQL Server 2000
   安装的任何SQL Server版本。 从SQL Server 2000开始
2. AdventureworksDW database is recommended, you can download it here. If you do not want to install it, you can use your own tables AdventureworksDW数据库，您可以在此处下载。 如果您不想安装它，则可以使用自己的表

入门 (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.

SQL相交运算符使我们能够在两个表或视图之间获取公共值。 下图显示了相交的作用。 集合论清楚地解释了相交的作用。

In mathematics, the intersection of A and B (A ∩ B) is the set that contains all elements of A that also belong to B.

在数学中，A和B的交集（A∩B）是包含A的所有元素的集合，这些元素也属于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 Server中，应用了相同的概念（可以说在SQL中，表是集合，并且可以在表和视图中应用所有Set理论）。

SQL相交样本 (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:

我们将使用AdventureworksDW表。 我们将使用2个表。 dbo.FactInternetSales和dbo.DimCurrency表。 我们将获得共同的要素。 首先让我们看一下dbo.FactInternetSales：

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.

请注意，该表具有CurrencyKey列，我们将使用此列获取该表与包含所有CurrencyKey ID的dbo.DimCurrency之间的公共值。

Now, let’s take a look at the dbo.DimCurrency table:

现在，让我们看一下dbo.DimCurrency表：

The currencykey is the common column between both tables, we will compare them and find the common values, the query will be this one:

currencykey是两个表之间的公共列，我们将对其进行比较并找到公共值，查询将是以下内容：

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:

这些值在两个表中都是通用的。 您可以比较多个列（如果适用），也可以获取3个或更多表之间的相交值。 我们将在下面显示这些方案：

如何使SQL与3个或更多表相交 (How to do a SQL intersect with 3 or more tables)

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

下面的示例将为此示例创建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.

该查询基于dbo.FactInternetSales的前5行和前7行创建名为table1和table2的2个表。

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

有了表格后，让我们运行示例：

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.

此示例将显示表dbo.Facinternetsales，dimcurrency，table1和table2之间的所有通用货币键。

SQL常见错误相交 (Common errors with SQL intersect)

A common error with SQL intersect is the following:

与SQL相交的常见错误如下：

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

消息245，级别16，状态1，行11将nvarchar值'yourvalue'转换为数据类型int时转换失败。

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

以下T-SQL代码可以生成错误消息：

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:

此错误消息表示您正在尝试将不兼容数据类型的值相交。 以下链接将显示T-SQL中兼容的数据类型：

• Download the compatibility chart 下载兼容性表

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

使用SQL相交时的另一个常见错误消息如下：

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.

消息205，级别16，状态1，第11行，所有使用UNION，INTERSECT或EXCEPT运算符组合的查询在其目标列表中必须具有相等数量的表达式。

The following query will produce the error message displayed:

以下查询将产生显示的错误消息：

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).

上面示例中的错误是显而易见的。 第一个选择（currencykey）中有一列，第二个选择（geographickey和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:

如果要计算表的列数，以下T-SQL查询可能会有用：

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.

上一个示例对存储在表dimEmployee的INFORMATION_SCHEMA.COLUMNS视图中的列进行计数。

SQL相交和SQL INNER连接之间的区别 (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 SQL内部联接的分步演练

The inner join will show common values between

内部联接将显示之间的通用值

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

让我们先看一下相交的结果：

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:

现在，让我们看一下内部联接：

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.

如果我们使用内部联接运行一个select different，则可能具有与使用intersect子句相同的值。

select distinct f.Currencykey from [dbo].[FactInternetSales] f
inner join dimcurrency d
on f.currencykey=d.currencykey

有关SQL相交的结论 (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相交的概念。 我们提醒集合论理解SQL相交概念，然后我们给出示例和常见错误。

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.

SQL相交是在视图或表之间获取公共值的一种选择。 最后，我们与内部联接进行比较，发现它是不同的，因为它不包含重复的值，因此它比较慢，因为它需要更多的精力来删除重复的值。

翻译自: https://www.sqlshack.com/sql-intersect-use-in-sql-server/