SQL 效率:WHERE IN 子查询 vs. JOIN 然后 GROUP

SQL Efficiency: WHERE IN Subquery vs. JOIN then GROUP(SQL 效率:WHERE IN 子查询 vs. JOIN 然后 GROUP)
本文介绍了SQL 效率:WHERE IN 子查询 vs. JOIN 然后 GROUP的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

例如,我想获取应用了特定标签的所有项目的列表.我可以执行以下任一操作:

As an example, I want to get the list of all items with certain tags applied to them. I could do either of the following:

SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
    SELECT ItemTag.ItemID
    FROM ItemTag
    WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)

SELECT Item.ID, Item.Name
FROM Item
LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
GROUP BY Item.ID, Item.Name

或者完全不同的东西.

总的来说(假设有一个通用规则),什么是更有效的方法?

In general (assuming there is a general rule), what's a more efficient approach?

推荐答案

SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
    SELECT ItemTag.ItemID
    FROM ItemTag
    WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)

SELECT Item.ID, Item.Name
FROM Item
LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
GROUP BY Item.ID

您的第二个查询将无法编译,因为它引用了 Item.Name 而没有对其进行分组或聚合.

Your second query won't compile, since it references Item.Name without either grouping or aggregating on it.

如果我们从查询中删除 GROUP BY:

If we remove GROUP BY from the query:

SELECT  Item.ID, Item.Name
FROM    Item
JOIN    ItemTag
ON      ItemTag.ItemID = Item.ID
WHERE   ItemTag.TagID = 57 OR ItemTag.TagID = 55

这些仍然是不同的查询,除非 ItemTag.ItemId 是一个 UNIQUE 键并被标记为这样.

these are still different queries, unless ItemTag.ItemId is a UNIQUE key and marked as such.

SQL Server 能够检测 UNIQUE 列上的 IN 条件,并且只会转换 IN> 条件转换为 JOIN.

SQL Server is able to detect an IN condition on a UNIQUE column, and will just transform the IN condition into a JOIN.

如果ItemTag.ItemID不是UNIQUE,第一个查询会使用一种SEMI JOIN算法,这种算法在SQL Server.

If ItemTag.ItemID is not UNIQUE, the first query will use a kind of a SEMI JOIN algorithm, which are quite efficient in SQL Server.

您可以将第二个查询转换为 JOIN:

You can trasform the second query into a JOIN:

SELECT  Item.ID, Item.Name
FROM    Item
JOIN    (
        SELECT DISTINCT ItemID
        FROMT  ItemTag
        WHERE  ItemTag.TagID = 57 OR ItemTag.TagID = 55
        ) tags
ON      tags.ItemID = Item.ID

但是这个比 INEXISTS 效率低一点.

but this one is a trifle less efficient than IN or EXISTS.

有关更详细的性能比较,请参阅我博客中的这篇文章:

See this article in my blog for a more detailed performance comparison:

  • IN vs. JOIN vs. EXISTS
  • IN vs. JOIN vs. EXISTS

这篇关于SQL 效率:WHERE IN 子查询 vs. JOIN 然后 GROUP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
SSIS: Model design issue causing duplications - can two fact tables be connected?(SSIS:模型设计问题导致重复-两个事实表可以连接吗?)
SQL Server Graph Database - shortest path using multiple edge types(SQL Server图形数据库-使用多种边类型的最短路径)
Invalid column name when using EF Core filtered includes(使用EF核心过滤包括时无效的列名)
How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)