SQL Server - 连接问题 - 3 个表
考虑 MSDN 文档 中的示例：
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
在此示例中，很明显左侧的表是“Production”，所有行都将从该表返回，然后仅返回与 ProductReview 匹配的行。
但现在考虑以下假设的查询，其中包含 3 个表 A、B、C。
select * from A
inner Join B on A.field1 = B.field1
left outer join C on C.field2 = b.Field2
该查询中的左表是哪个（无论是否与 C 匹配，都将从该表返回所有记录）？是A还是B？或者是 A & 联接的结果？乙？
我的困惑来自以下 MSDN 文档，其中指出“外部联接只能在 FROM 子句中指定”，这意味着我的假设查询中的左表是 A，但是我没有指定连接条件的 ON 子句 - 在这种情况下，我的假设查询是一个坏查询吗？
Consider the example from MSDN documentation:
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
In this example, it is clear that the table on the left is "Production" and that is where all rows will be returned from, and then only those that match in ProductReview.
But now consider the following hypothetical query with 3 tables A,B,C
select * from A
inner Join B on A.field1 = B.field1
left outer join C on C.field2 = b.Field2
Which is the left table in this query (from which all records will be returned, regardless of a match to C)? Is it A or B? Or is it the result of the join from A & B?
My confusion arises from the following MSDN documentation, which states that "Outer joins can be specified in the FROM clause only" which would mean that the left table in my hypothetical query is A, but then I dont have an ON clause that specifies the join condition - in which case is my hypothetical query a bad one?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
由于 A 和 B 之间存在 INNER JOIN，因此只有 B 中与 A 匹配的行才有资格进行到 C 的 LEFT JOIN。
Since there is an INNER JOIN between A and B, only rows from B that match A will qualify for the LEFT JOIN to C.
我不是 100% 确定我理解你的问题，但假设我正确理解它：
假设查询中的“左”表是 B，因为你的 ON 条件指定了 B.Field2。
I'm not 100% sure I understand you question, but assuming I am understanding it correctly:
Your "left" table in your hypothetical query is B, since your ON condition specifies the B.Field2.
在此上下文中，术语“左”和“右”不够具体。相反，您应该使用术语“保留”和“未保留”。鉴于此，表 A 和 B 被保留，表 C 未被保留。
参考MSDN 文档中的内容意味着您不能在子查询之外的 Select、Where、Group By、Having 或 Order By 子句中使用联接（外部联接或其他联接）（它们仍位于 From 子句中）。
The terms 'left" and "right" are not sufficiently specific in this context. Instead, you should use the terms "preserved" and "unpreserved". In that light, tables A and B are preserved and table C is unpreserved.
The reference in the MSDN documentation is meant to imply you cannot use joins (outer or otherwise) in the Select, Where, Group By, Having or Order By clauses outside of a subquery (where they are still in a From clause).
从您的联接中
您需要从表
A和B中获取记录。左连接仅包含与
B表匹配的表C字段field2中的数据，但请注意表A< code>field2 不必匹配。要查看表
C的数据，请运行以下命令：From your joins
You need to have records from table
AandB.The left join only has data from table
Cfieldfield2matching theBtable, but note that tableAfield2does not have to match.To see your data for table
Crun the following:他们在一般（广义）意义上使用术语FROM子句，表示从关键字
FROM开始并包括所有连接的查询的整个部分。这是更完整的上下文（请注意前面的句子）：
看到了吗？它们意味着您不能像内连接那样在 WHERE 子句中指定外连接。您只能在 FROM 子句中执行此操作（即，在许多其他连接之后）。结果将应用于先前连接的结果。
They use the term FROM clause in a general (broad) sense meaning the whole section of the query that starts from the keyword
FROMand includes all the joins there are.Here's a fuller context (note the previous sentence):
See? They mean you cannot specify an outer join in the WHERE clause as is the case with inner joins. You can only do that in the FROM clause (that is, after however many other joins too). The result will be applied to the result of the previous joins.