INNER JOIN 和 LEFT/RIGHT OUTER JOIN 的问题

2021-12-17 00:00:00 join sql tsql sql-server

我有三张桌子:

  • 订单
    • 订单 ID，整数 PK
    • CustomerId, int FK to Customer, NULL 允许


    • 客户
      • 客户 ID，整数 PK
      • CompanyId, int FK to Company, NULL 不允许


      • 公司
        • 公司 ID，整数 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 之间的关系是必需的:客户必须em> 有一家公司.

        ...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

        您正在执行内连接，因此值必须出现在两侧.在您的情况下，这会取消 左外连接 的效果.

        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 或[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

相关文章