INNER JOIN和LEFT / RIGHT OUTER JOIN的问题

I have three tables:

我有三张桌子：

Orders
- OrderId, int PK
- OrderId，int PK
- CustomerId, int FK to Customer, NULL allowed
- CustomerId，int FK to Customer，允许NULL
订单OrderId，int PK CustomerId，int FK to Customer，允许NULL

Customers
- CustomerId, int PK
- CustomerId，int PK
- CompanyId, int FK to Company, NULL not allowed
- CompanyId，int FK to Company，NULL不允许
客户CustomerId，int PK CompanyId，int FK to Company，不允许NULL

Companies
- CompanyId, int PK
- CompanyId，int PK
- Name, nvarchar(50)
- 名称，nvarchar（50）
公司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

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

...它只返回有客户的订单。如果我用LEFT OUTER JOIN替换INNER 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

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

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.

这个查询有我期望的内部和外部联接的数量，但问题是它很难为我阅读，因为我将查询作为订单的查询，其中订单是选择的“根”而不是公司。对我来说，使用RIGHT OUTER JOIN并不熟悉。

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.

最后一个查询是设计器为SQL Server Reporting Services报告生成的查询的一小部分。我试图在没有设计器表面的情况下手动编写查询，因为它非常过度拥挤，并且在将来需要进行多次更改和更多更改后，我遇到了维护查询的问题。所以，我想以某种方式给查询一个可读的结构。

Questions:

问题：

Why doesn't query 1 work as I expected?
为什么不按我的预期查询1的工作？
Is query 2 the correct solution although (or because?) it uses two LEFT OTHER JOINS?
查询2是正确的解决方案虽然（或因为？）它使用两个LEFT OTHER JOINS？
Is query 3 the correct solution?
查询3是正确的解决方案吗？
Is there a better way to write the query?
有没有更好的方法来编写查询？
Are there some general rules of thumb and practices how to write a query with a lot of outer and inner joins in a good readable manner?
是否有一些通用的经验法则和实践如何以良好的可读方式编写具有大量外部和内部联接的查询？

5 个解决方案

#1

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子句中出现的顺序进行处理。（由于SQL优化，它们可能实际上不按此顺序执行，但排序对于定义结果集很重要。）

So, when you do:

所以，当你这样做时：

from orders left outer join customers inner join companies

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

（我正在遗漏那些为此目的分散注意力的条款。）

The SQL is interpreted as:

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.

您正在进行内部联接，因此值必须出现在两侧。在您的情况下，这会撤消左外连接的效果。

You want:

你要：

from orders left outer join (customers inner join companies)

Here are some solutions.

这是一些解决方案。

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.

我首选的解决方案是对所有连接使用左外连接。事实上，为了可读性和可维护性，我写的几乎每个查询都只是左外连接或连接表的[inner]连接。如果您可以以一致的形式编写查询，那么必须解析查询以理解联接的语义似乎是不必要的努力。

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

#2

Query 1: Because you have an INNER JOIN on Customers, the LEFT JOIN is effectively an INNER JOIN.
查询1：因为您在客户上有一个INNER JOIN，所以LEFT JOIN实际上是一个INNER JOIN。
Query 2 is correct because you want to see all Orders regardless of the data quality / condition.
查询2是正确的，因为无论数据质量/条件如何，您都希望查看所有订单。
I like to avoid RIGHT JOINs in general as it is confusing to some developers and is therefore less readable. You can generally write your query in such a way to do the same thing with effective use of LEFT JOINs.
我喜欢避免使用RIGHT JOIN，因为它让一些开发人员感到困惑，因此不太可读。您通常可以通过有效使用LEFT JOIN来执行相同的操作来编写查询。
Query 2 is my recommendation for something simple like this.
查询2是我对这样简单的推荐。
One general rule... Once you introduce an OUTER JOIN into your query, the JOINs that follow should also be OUTER JOINs. Otherwise, you MAY exclude rows you did not intend.
一般规则...一旦将OUTER JOIN引入查询，后面的JOIN也应该是OUTER JOIN。否则，您可以排除您不想要的行。

#3

You can write your joins nested like this so that the left join is performed on the combined result of customers and companies instead of an inner join being performed on the combined result of orders and customers. I basically just moved your inner join to before the ON clause for the left outer join. Someone else suggested parenthesis to get this result, both syntaxes will result in the same execution if memory serves.

您可以像这样编写嵌套连接，以便对客户和公司的组合结果执行左连接，而不是对订单和客户的组合结果执行内连接。我基本上只是将内连接移动到左外连接的ON子句之前。其他人建议使用括号来获得此结果，如果内存服务，两种语法都将导致相同的执行。

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

#4

Query 1 have INNER JOIN on Company , which means a Order need to have vaild Customer(CompanyID) If you want to use INNER JOIN, it can be like this

查询1在公司上有INNER JOIN，这意味着订单需要有vaild Customer（CompanyID）如果你想使用INNER JOIN，它可以是这样的

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

#5

1) It doesn't work because when you INNER JOIN to Companies you make it required to exist in the entirety of the join, but since Customer does not exist for the order there is no way to associate a Companies record back to the order and thus it is not returned.

1）它不起作用，因为当您内联到公司时，您需要在整个联接中存在，但由于订单不存在客户，因此无法将公司记录与订单相关联，因此它不会被退回。

2) I suppose you could use the second query if you're ok getting Customer records with no related company, but if the relation between those tables is 1 to 1 it should be fine.

2）我想你可以使用第二个查询，如果你没有相关公司的客户记录，但如果这些表之间的关系是1比1，它应该没问题。

3) The third query is fine, but ugly. You join the company and customer tables and then say that regardless of what is in that resultset I want everything from Orders.

3）第三个查询很好，但很难看。您加入公司和客户表，然后说不管结果集中有什么，我想要订单中的所有内容。

4) I would probably join customers and companies in a subquery and left join that back to orders.

4）我可能会加入子查询中的客户和公司，并将其重新加入订单。

Query:

查询：

SELECT  Orders.OrderId, 
        Subquery.CustomerId, 
        Subquery.Name
FROM    Orders
LEFT    OUTER JOIN 
       (Select  Customers.CustomerID,
                Companies.Name
        From    Customers
        INNER   JOIN Companies
                ON Customers.CompanyId = Companies.CompanyId) Subquery
        On Orders.CustomerID = Subquery.CustomerID

5) This is much more easily answered with a google search. I'm sure there is more comprehensive info that I could write in a couple minutes.

5）谷歌搜索更容易回答这个问题。我确信我可以在几分钟内写出更全面的信息。

#1