How does a SQL statement containing mutiple joins work?

Question

I study joins in my class, but I do not quite understand some concepts. Can someone explain how an operator works with multiple joins?

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#; 

I suppose 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 no CUSTOMER for the book. After the first connection, I'm not sure what is really going on.

When ORDERS is joined, which is the left table and is the right table? The same is for Customers. Here I am lost.

Answer 1

The first thing that the contractor will do is take the first pair of tables that can be combined and join. In the next steps, the result of the previous join is considered as a virtual relation, so again you have a construction similar to ... FROM virt_tab LEFT JOIN real_tab ... This behavior is based on the concept used in Relational Algebra , which means that any operation with respect to a relation creates a relation, i.e. operations can be nested. And RDBMS means relational DBMS, look at the related wikipedia article.

Until now, I believe that PostgreSQL documents are the most specific in this matter, take a look at them . In a related article, a general overview of how joins are performed by databases is given with some of the PostrgeSQL-specific materials that are expected.

Answer 2

One of my favorite online resources: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Regarding your question.

All books will be displayed, and only those serial numbers that correspond to the book will be displayed only those orders that have a corresponding entry in the orderitems that belong to the book Only customers who have orders with items in the book table will be listed.

Thus, customers who do not have orders will not be listed Customers who have orders, but for items that are not books, will NOT be listed.

Funny stuff. I hope, you like it.

Regarding your second question: Right / left is just a question due to the ORDER tables in your statement from. You can make each join on the left if you arrange the order of the tables. All right / left do indicates the table from which you want ALL records.

Consider: you could just as easily select your select statement:

SELECT B.TITLE, O.ORDER #, C.STATE FROM CLIENTS C
CORRECT INTERACTION ORDER O TO C.CUSTOMER # = O.CUSTOMER # CORRECT ENTRANCE JOIN ORDER OI TO O.ORDER # = OI.ORDER # CORRECT EXTERNAL ENTRANCE TO BOOKS B TO B.ISBN = OI

In this case, the right says that I want all the entries from the table on the right, since the books are the last in the list, you will receive all the books and only those ordered items associated with the book, only those orders for which the ordered item was a book, and only those customers with orders for items ordered that were books. Thus, the left / right is the same, with the exception of order. I avoid the right mix for readability. It’s easier for me to go down, thinking about what is on and what will not.

Those records that are excluded will have NULL values ​​in these types of joins.

Hope this helps.