T-SQL命令性能比较– NOT IN与SQL NOT EXISTS与SQL LEFT JOIN与SQL EXCEPT

This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.

本文为您提供了NOT IN，SQL Not Exists，SQL LEFT JOIN和SQL EXCEPT的性能比较。

The T-SQL commands library, available in Microsoft SQL Server and updated in each version with new commands and enhancements to the existing commands, provides us with different ways to perform the same action. In addition to an ever evolving toolkit of commands, different developers will apply different techniques and approaches to the same problem sets and challenges

T-SQL命令库在Microsoft SQL Server中可用，并且在每个版本中都通过新命令和对现有命令的增强进行了更新，为我们提供了执行相同操作的不同方法。 除了不断发展的命令工具包之外，不同的开发人员还将对相同的问题集和挑战采用不同的技术和方法。

For example, three different SQL Server developers can get the same data using three different queries, with each developer having his own approach to writing the T-SQL queries to retrieve or modify the data. But the database administrator will not be necessarily be happy with all of these approaches, he is looking to these methods from different aspects that they may not concentrate on. Although all of them may get the same required result, each query will behave in a different way, consume a different amount of SQL Server resources with different execution times. All of these parameters that the database administrator concentrates on shape the query performance. And it is the database administrator’s rule here to tune the performance of these queries and choose the best method with the minimum possible effect on the overall SQL Server performance.

例如，三个不同SQL Server开发人员可以使用三个不同的查询来获取相同的数据，每个开发人员都有自己的编写T-SQL查询的方法来检索或修改数据。 但是数据库管理员不一定会对所有这些方法都满意，他正在从可能不专心的各个方面来寻找这些方法。 尽管所有查询都可能获得相同的所需结果，但是每个查询的行为都将有所不同，并以不同的执行时间消耗不同数量SQL Server资源。 数据库管理员集中在所有这些参数上来影响查询性能。 这是数据库管理员的规则，它可以调整这些查询的性能并选择对SQL Server整体性能的影响最小的最佳方法。

In this article, we will describe the different ways that can be used to retrieve data from a table that does not exist in another table and compare the performance of these different approaches. These methods will use the NOT IN, SQL NOT EXISTS, LEFT JOIN and EXCEPT T-SQL commands. Before starting the performance comparison between the different methods, we will provide a brief description of each one of these T-SQL commands.

在本文中，我们将描述可用于从另一个表中不存在的表中检索数据的不同方法，并比较这些不同方法的性能。 这些方法将使用NOT IN ， SQL NOT EXISTS ， LEFT JOIN和EXCEPT T-SQL命令。 在开始比较不同方法之间的性能之前，我们将对这些T-SQL命令中的每一个进行简要说明。

The SQL NOT IN command allows you to specify multiple values in the WHERE clause. You can imagine it as a series of NOT EQUAL TO commands that are separated by the OR condition. The NO IN command compares specific column values from the first table with another column values in the second table or a subquery, and returns all values from the first table that are not found in the second table, without performing any filter for the distinct values. The NULL is considered and returned by the NOT IN command as a value.

SQL NOT IN命令允许您在WHERE子句中指定多个值。 您可以将其想象为一系列由OR条件分隔的NOT EQUAL TO命令。 NO IN命令将第一个表中的特定列值与第二个表或子查询中的另一个列值进行比较，并返回第二个表中未找到的第一个表中的所有值，而无需对不同的值执行任何过滤。 NULL由NOT IN命令考虑并作为值返回。

The SQL NOT EXISTS command is used to check for the existence of specific values in the provided subquery. The subquery will not return any data; it returns TRUE or FALSE values depend on the subquery values existence check.

SQL NOT EXISTS命令用于检查提供的子查询中是否存在特定值。 子查询将不返回任何数据； 它返回TRUE或FALSE值取决于子查询值的存在性检查。

The LEFT JOIN command is used to return all records from the first left table, the matched records from the second right table and NULL values from the right side for the left table records that have no match in the right table.

LEFT JOIN命令用于返回第一个左表中的所有记录，第二个右表中的匹配记录以及右表中不匹配的左表记录的右侧NULL值。

The EXCEPT command is used to return all distinct records from the first SELECT statement that are not returned from the second SELECT statement, with each SELECT statement will be considered as a separate dataset. In other words, it returns all distinct records from the first dataset and remove from that result the records that are returned from the second dataset. You can imagine it as a combination of the SQL NOT EXISTS command and the DISTINCT clause. Take into consideration that the left and the right datasets of the EXCEPT command should have the same number of columns.

EXCEPT命令用于返回第一个SELECT语句中未从第二个SELECT语句中返回的所有不同记录，而每个SELECT语句将被视为一个单独的数据集。 换句话说，它从第一个数据集返回所有不同的记录，并从结果中删除从第二个数据集返回的记录。 您可以将其想象为SQL NOT EXISTS命令和DISTINCT子句的组合。 考虑到EXCEPT命令的左和右数据集应具有相同的列数。

Now, let us see, in practical terms, how we could retrieve data from one table that does not exist in another table using different methods and compare the performance of these methods to conclude which one behaves in the best way. We will start with creating two new tabl