太多的Left Joins代码有异味吗? [英] Is too many Left Joins a code smell?

问题描述

例如，如果您有> 5个查询中的左联接是一个代码闻到了...

If you have for example > 5 left joins in a query is that a code smell that there is ...

• 您的设计有问题吗?
• 您在一个查询中做得太多了吗?
• 您的数据库过于规范化了吗?

推荐答案

对于某些设计，这是一个完全合法的解决方案.

It's a perfectly legitimate solution for some designs.

假设您具有一对多关系的层次结构，例如Customer-Order-Basket-Item-Price等，可以在任何级别上填充:可能没有Orders，Order可能没有Baskets，依此类推.

Say you have a hierarchy of one-to-many relations like Customer - Order - Basket - Item - Price, etc., which can be unfilled on any level: a Customer may have no Orders, an Order can have no Baskets, etc.

在这种情况下，您发出类似以下内容的信息:

In this case you issue something like:

SELECT  *
FROM    Customer c
LEFT OUTER JOIN
        Order o
ON      o.CustomerID = c.ID
LEFT OUTER JOIN
        Basket b
ON      b.OrderID = c.ID
…

请注意，在某些情况下它可能效率不高，并且可以用EXISTS或NOT EXISTS代替(如果您只想确定对应的记录在其他表中存在或不存在).

Note that it may be inefficient in some cases, and may be replaced with EXISTS or NOT EXISTS (if you only want to figure out that the corresponding records exist or do not exist in other tables).

有关性能的详细信息，请参阅我的博客中的这篇文章:

See this article in my blog for performance details:

• 查找不完整的订单 -如何受益于用NOT EXISTS
替换LEFT JOIN

这篇关于太多的Left Joins代码有异味吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！