INNER JOIN和IN的区别

Question

SELECT C.* FROM StockToCategory STC 
INNER JOIN Category C ON STC.CategoryID = C.CategoryID 
WHERE STC.StockID = @StockID

VS

SELECT * FROM Category
WHERE CategoryID IN
    (SELECT CategoryID FROM StockToCategory WHERE StockID = @StockID)

哪种方法被认为是（句法上）正确且性能最佳的方法？为什么？

对我来说，后一种示例中的语法似乎更合乎逻辑，但我的假设是JOIN会更快。

我查看了查询计划，但没有从中解密出任何信息。

查询计划1
查询计划2

- Maxim Gershkovich

---

如果两者均可以执行，则两者在语法上都是正确的。对我而言，这似乎是经典的连接与子查询问题... - BoltClock

好的，观点已经被记录下来了，但我确定这里的SQL专家会说一个比另一个“更”正确。 - Maxim Gershkovich

为什么不将两个查询放入 SSMS 查询窗口中并一起执行呢？确保“包括实际执行计划”-SSMS 将显示哪个查询占用了多少百分比的总执行时间。如果您有 50%:50% 的绘图-那么两者几乎相等。如果一个表现得更差，SSMS 将非常好地显示出来。 - marc_s

如果在StockToCategory中有(CategoryID, StockID)的唯一约束或主键，则查询相同。否则，查询结果可能会有所不同。 - Mikael Eriksson

Answer 1

两种语法用途不同。使用Join语法假定您想要从StockToCategory和Category表中获取某些内容。如果StockToCategory表中对于每个类别存在多个条目，则Category表中的值将被重复显示。

使用IN函数假定您只想从符合某些条件的ID的Category中获取项目。如果在StockToCategory表中给定的CategoryId（假设它是Category表的主键）多次出现，则仅返回一次。

在您的确切示例中，它们将产生相同的输出，但在我看来，后面的语法使您的意图（只想要类别）更加清晰。

顺便说一下，还有第三种类似于使用IN函数的语法：

Select ...
From Category
Where Exists    (
                Select 1
                From StockToCategory
                Where StockToCategory.CategoryId = Category.CategoryId
                    And StockToCategory.Stock = @StockId
                )

- Thomas

---

对于这个变体，我认为“... EXISTS (SELECT * ...)”是更好的语法。 - Serguei

+1是为了指出JOIN可能会复制Category行，这才是这些查询之间的真正区别。 - Serguei

@yazanpro - 这个问题的主要焦点是结果的正确性。如果没有更多关于数据、数据库版本、索引等的信息，就不能对任何解决方案的性能做出陈述。无论采用什么方法，在未评估执行计划和IO统计数据之前，我们都不能确定哪种解决方案的性能更好。 - Thomas

Answer 2

从语法（语义）上来说，这两种写法都是正确的。就性能而言，它们在实际效果上是等价的，事实上我预计SQL Server会为这两个查询生成完全相同的物理计划。

- Serguei

---

那是我的第一直觉，但他们不这样做！我会在网上发布查询计划，这样你们就可以看看了... - Maxim Gershkovich

@Maxim：你能否也发布表的定义？（包括索引、外键）。另外，生成这些计划的SQL Server版本是哪个？ - Serguei

Answer 3

针对sqlite

表device_group_folders包含10条记录

表device_groups包含约100000条记录

INNER JOIN: 31毫秒

WITH RECURSIVE select_childs(uuid) AS (
SELECT uuid FROM device_group_folders WHERE uuid = '000B:653D1D5D:00000003'
UNION ALL
SELECT device_group_folders.uuid FROM device_group_folders INNER JOIN select_childs ON parent = select_childs.uuid
) SELECT device_groups.uuid FROM select_childs INNER JOIN device_groups ON device_groups.parent = select_childs.uuid;

执行时间 31 毫秒

WITH RECURSIVE select_childs(uuid) AS (
    SELECT uuid FROM device_group_folders WHERE uuid = '000B:653D1D5D:00000003'
UNION ALL
SELECT device_group_folders.uuid FROM device_group_folders INNER JOIN select_childs ON parent = select_childs.uuid
) SELECT device_groups.uuid FROM select_childs, device_groups WHERE device_groups.parent = select_childs.uuid;

在小于1毫秒内

SELECT device_groups.uuid FROM device_groups WHERE device_groups.parent IN (WITH RECURSIVE select_childs(uuid) AS (
    SELECT uuid FROM device_group_folders WHERE uuid = '000B:653D1D5D:00000003'
    UNION ALL
    SELECT device_group_folders.uuid FROM device_group_folders INNER JOIN select_childs ON parent = select_childs.uuid
) SELECT * FROM select_childs);

- kaegoorn48

Answer 4

我认为有两种方法可以指定相同的期望结果。

- Pankaj Agarwal