包含多个连接的SQL语句如何工作？ [英] How does a SQL statement containing mutiple joins work?

问题描述

我在我的课上学习加入，但我并没有完全理解一些概念。

I'm learning joins in my class, but I'm not fully grasping some of the concepts. Can somebody explain how a statement with multiple joins works?

SELECT B.TITLE, O.ORDER#, C.STATE FROM BOOKS B
   LEFT OUTER JOIN ORDERITEMS OI ON B.ISBN = OI.ISBN
   LEFT OUTER JOIN ORDERS O ON O.ORDER# = OI.ORDER#
   LEFT OUTER JOIN CUSTOMERS C ON C.CUSTOMER# = O.CUSTOMER#;

我相信我理解，BOOKS表是连接BOOKS和ORDERITEMS的第一个外连接中的左表。将显示所有BOOKS，即使没有针对某本书的ORDERITEM。第一次加入后，我不知道发生了什么。

I believe I understand that the BOOKS table is the left table in the first outer join connecting BOOKS and ORDERITEMS. All BOOKS will be shown, even if there is not an ORDERITEM for a book. After the first join, I'm not sure what is really happening.

当ORDERS加入时，即左表，哪一个是正确的表？同样的客户。这是我迷路的地方。

When ORDERS is joined, which is the left table and which is the right table? The same for Customers. This is where I get lost.

推荐答案

执行器将执行的第一件事 - 加入并执行连接。在以下步骤中，先前连接的结果将被视为一个虚拟关系，因此您还有一个类似于 ...的构造...从virt_tab LEFT JOIN real_tab ... 。此行为是基于 关闭概念 使用的关系代数，这意味着关系上的任何操作都会产生关系，即操作可以嵌套。

First thing what executor will perform — take a first pair of tables that are eligible to be joined and perform the join. On the following steps, the result of the previous join is treated as a virtual relation, therefore you again have a construct similar to ... FROM virt_tab LEFT JOIN real_tab .... This behavior is based on the closure concept used in Relational Algebra, which means that any operation on the relation produces relation, i.e. operations can be nested. And RDBMS stands for Relational DBMS, take a look at the linked wikipedia article.

到目前为止，我发现PostgreSQL的文档在这个问题上是最明确的，查看。在链接的文章中，一些关于如何由数据库执行连接的一般概述是与一些PostrgeSQL特定的东西给出的，这是预期的。

So far I find PostgreSQL's docs being most definitive in this matter, take a look at them. In the linked article a generic overview on how joins are performed by the databases is given with some PostrgeSQL-specific stuff, which is expected.

这篇关于包含多个连接的SQL语句如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！