在SQL中是2选择还是1选择+ 1连接? [英] Two selects or one select + one join in SQL?

问题描述

以下代码段应该执行相同的工作.

The following code snippets should do the same work.

SELECT t1.* FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.IDService
WHERE t2.Code = @code

和

SELECT * FROM table1 t1
WHERE t1.ID IN (SELECT IDService FROM table2 WHERE Code = @code)

一般来说，哪个是最好的解决方案?并且在计算上，最好是具有两个嵌套选择，还是更好地使用内部联接?

Which one is the best solution, in general? And computationally, is better have two nested select or is better use inner join?

考虑到table1的PK是ID，而table2的PK是一对(IDService,Code).因此，修复代码(使用WHERE子句)并将子句ON应用于IDService，我可以假定每次选择的结果都相同.

Consider that the PK of table1 is ID and the PK of table2 id the couple (IDService,Code). So, fixing the Code (using WHERE clause) and applying the clause ON to IDService, I can assume that the result of each select are the same.

推荐答案

您认为他们应该做相同工作的想法是不正确的.想象一下这个测试数据集:

Your notion that they should do the same work is not true. Imagine this test set of data:

T1

ID
----
1
2
3
4
5

T2

ID
---
1
1
1
2
2
3

DDL

CREATE TABLE dbo.T1 (ID INT NOT NULL);
INSERT dbo.T1 (ID) VALUES (1), (2), (3), (4), (5);

CREATE TABLE dbo.T2 (ID INT NOT NULL);
INSERT dbo.T2 (ID) VALUES (1), (1), (1), (2), (2), (3);

SELECT  *
FROM    dbo.T1
WHERE   T1.ID IN (SELECT T2.ID FROM dbo.T2);

SELECT  T1.*
FROM    dbo.T1
        INNER JOIN dbo.T2
            ON T1.ID = T2.ID;

结果

ID
---
1
2
3

ID
---
1
1
1
2
2
3

只有当您搜索的列是唯一的时，您的结果才是相同的.

Your results are only the same if the column you are searching in is unique.

CREATE TABLE dbo.T1 (ID INT NOT NULL);
INSERT dbo.T1 (ID) VALUES (1), (2), (3), (4), (5);

CREATE TABLE dbo.T2 (ID INT NOT NULL);
INSERT dbo.T2 (ID) VALUES (1), (2), (3);

SELECT  *
FROM    dbo.T1
WHERE   T1.ID IN (SELECT T2.ID FROM dbo.T2);

SELECT  T1.*
FROM    dbo.T1
        INNER JOIN dbo.T2
            ON T1.ID = T2.ID;

即使结果相同，执行计划也不一样.使用IN的第一个查询能够使用反半连接，这意味着它知道不需要t2中的数据，因此，一旦找到单个匹配项，它就可以停止扫描进一步的匹配项.

Even though the results are the same, the execution plan isn't. The first query using IN is able to use an anti-semi join, meaning it knows that the data in t2 is not needed, so as soon as it finds a single match it can stop scanning for further matches.

如果将第二个表约束为仅具有唯一值，那么您将看到相同的计划:

If you constrain your second table to have only unique values, then you will see the same plan:

CREATE TABLE dbo.T1 (ID INT NOT NULL PRIMARY KEY);
INSERT dbo.T1 (ID) VALUES (1), (2), (3), (4), (5);

CREATE TABLE dbo.T2 (ID INT NOT NULL PRIMARY KEY);
INSERT dbo.T2 (ID) VALUES (1), (2), (3);

SELECT  *
FROM    dbo.T1
WHERE   T1.ID IN (SELECT T2.ID FROM dbo.T2);

SELECT  T1.*
FROM    dbo.T1
        INNER JOIN dbo.T2
            ON T1.ID = T2.ID;

总而言之，这两个查询将不会总是产生相同的结果，并且它们也不会总是具有相同的计划.这实际上取决于您的索引和数据/查询的宽度.

In summary, the two queries will not always yield the same results, and they will not always have the same plan. It really depends on your Indexes and the width of your data/query.

这篇关于在SQL中是2选择还是1选择+ 1连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！