内联接和左/右外联接的问题 [英] Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN

问题描述

我有三个表:

• 订单
  • OrderId，整数PK
  • CustomerId，对客户的int FK，允许为NULL

  
  

  • 客户
    • CustomerId，整数PK
    • CompanyId，将FK转换为Company，不允许为NULL

    
    

    • 公司
      • CompanyId，int PK
      • 名称，nvarchar(50)

      我想选择所有订单，无论是否有客户，如果有客户，也要选择客户的公司名称.

      I want to select all orders, no matter if they have a customer or not, and if they have a customer then also the customer's company name.

      如果我使用此查询...

      If I use this query...

      SELECT Orders.OrderId, Customers.CustomerId, Companies.Name
      FROM   Orders
             LEFT OUTER JOIN Customers
                 ON Orders.CustomerId = Customers.CustomerId
             INNER JOIN Companies
                 OM Customers.CompanyId = Companies.CompanyId
      

      ...它仅返回有客户的订单.如果我将INNER JOIN替换为LEFT OUTER JOIN ...

      ...it only returns the orders that have a customer. If I replace INNER JOIN by LEFT OUTER JOIN...

      SELECT Orders.OrderId, Customers.CustomerId, Companies.Name
      FROM   Orders
             LEFT OUTER JOIN Customers
                 ON Orders.CustomerId = Customers.CustomerId
             LEFT OUTER JOIN Companies
                 OM Customers.CompanyId = Companies.CompanyId
      

      ...有效，但我不明白为什么这样做是必要的，因为Customers和Companies之间的关系是必需的:客户必须有一家公司.

      ...it works but I don't understand why this is necessary because the relationship between Customers and Companies is required: A customer must have a company.

      一种可行的替代方法似乎是:

      An alternative approach which works as well seems to be:

      SELECT Orders.OrderId, Customers.CustomerId, Companies.Name
      FROM   Companies
             INNER JOIN Customers
                 ON Companies.CompanyId = Customers.CompanyId
             RIGHT OUTER JOIN Orders
                 OM Customers.CustomerId Orders.CustomerId
      

      此查询具有我期望的内部和外部联接的数量，但问题是对我来说很难阅读，因为我将我的查询作为对 orders 的查询是选择的根"，而不是公司.而且RIGHT OUTER JOIN的用法对我来说还很陌生.

      This query has the number of inner and outer joins that I expect but the problem is that it is hard to read for me because I have my query as a query of orders in mind where an order is the "root" of the selection and not the company. Also the usage of RIGHT OUTER JOIN is rather unfamiliar to me.

      最后一个查询是设计器为SQL Server Reporting Services报表生成的查询的一小部分.我正在尝试在没有设计人员界面的情况下手动编写查询，因为它非常拥挤，并且在进行许多更改并且将来可能会有更多更改之后，在维护查询方面遇到了问题.因此，我想以某种方式为查询提供可读的结构.

      The last query is a small part of a query generated by the designer for SQL Server Reporting Services Reports. I am trying to write the query manually without the designer surface because it is very overcrowded and I'm having problems to maintain the query after many changes and more changes are expected in the future. So, I want to give the query a readable structure somehow.

      问题:

      1. 为什么不查询1个我期望的工作?
      2. 查询2是否是正确的解决方案，尽管(或因为?)它使用了两个LEFT OTHER JOINS?
      3. 查询3是正确的解决方案吗?
      4. 是否有更好的方法编写查询?
      5. 是否存在一些一般的经验法则和实践，如何以一种易于阅读的方式编写具有大量外部和内部联接的查询?

      推荐答案

      在语义上，按照在from子句中出现的顺序处理连接. (由于SQL优化，它们可能实际上未按此顺序执行，但是该顺序对于定义结果集很重要.)

      Semantically, joins are processed in the order they appear in the from clause. (They may not be actually executed in this order due to SQL optimizations, but the ordering is important for defining the result set.)

      所以，当您这样做时:

      from orders left outer join customers inner join companies
      

      (我省去了on子句，因为这会分散他们的注意力.)

      (I'm leaving out the on clauses which are a distraction for this purpose.)

      SQL解释为:

      from (orders left outer join customers) inner join companies
      

      您正在执行inner join，因此值必须同时出现在两侧.就您而言，这会取消left outer join的效果.

      You are doing an inner join, so the values must appear on both sides. In your case, this undoes the effect of the left outer join.

      您想要:

      from orders left outer join (customers inner join companies)
      

      以下是一些解决方案.

      我的首选解决方案是对所有联接使用left outer join.实际上，出于可读性和可维护性，我编写的几乎每个查询都只会是left outer join或[inner] join连接表.如果您可以以一致的形式编写查询，则必须解析查询以了解联接的语义似乎是不必要的工作.

      My preferred solution is to use left outer join for all the joins. In fact, for readability and maintainability, almost every query I write is going to be only left outer join or [inner] join connecting the tables. Having to parse through the query to understand the semantics of the joins seems to be an unnecessary effort, if you can write the queries in a consistent form.

      另一种解决方案是使用括号:

      Another solution is to use parentheses:

      from orders left outer join (customers inner join companies)
      

      另一个解决方案是子查询:

      Another solution is a subquery:

      from orders left outer join (select . . . from customers inner join companies) cc
      

      这篇关于内联接和左/右外联接的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！