内连接与内连接 [英] INNER JOIN vs IN

问题描述

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)

哪种被认为是正确(在语法上)和最高效的方法，为什么?

Which is considered the correct (syntactically) and most performant approach and why?

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

The syntax in the latter example seems more logical to me but my assumption is the JOIN will be faster.

我查看了查询计划，但无法从中破译任何内容.

I have looked at the query plans and havent been able to decipher anything from them.

查询计划 1
查询计划 2

推荐答案

这两种语法用于不同的目的.使用 Join 语法假定您需要 StockToCategory 和 Category 表中的某些内容.如果每个类别的 StockToCategory 表中有多个条目，则类别表的值将重复.

The two syntaxes serve different purposes. Using the Join syntax presumes you want something from both the StockToCategory and Category table. If there are multiple entries in the StockToCategory table for each category, the Category table values will be repeated.

使用 IN 函数假定您只需要 ID 满足某些条件的类别中的项目.如果给定的 CategoryId(假设它是 Category 表的 PK)在 StockToCategory 表中多次存在，则只会返回一次.

Using the IN function presumes that you want only items from the Category whose ID meets some criteria. If a given CategoryId (assuming it is the PK of the Category table) exists multiple times in the StockToCategory table, it will only be returned once.

在您的确切示例中，它们将产生相同的输出，但 IMO，后面的语法使您的意图(只想要类别)更清晰.

In your exact example, they will produce the same output however IMO, the later syntax makes your intent (only wanting categories), clearer.

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

Btw, yet a third syntax which is similar to using the IN function:

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

这篇关于内连接与内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！