2

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

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.

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.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Frankie
  • 2,235
  • 4
  • 21
  • 22

2 Answers2

5

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.

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.

vyegorov
  • 21,787
  • 7
  • 59
  • 73
  • +1 Worth pointing out that it is the placement of the `ON` clause rather than the tables that controls the logical ordering of joins as well. – Martin Smith Jun 25 '12 at 13:14
  • @vyegorov I didn't realize it took the set from the first join and then used that "virtual set" against the next join. Thanks! – Frankie Jun 25 '12 at 14:12
0

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

As to your question.

All books will be displayed and only those orderitems which match a book all only those orders which have a related record in orderitems which relate to a book will be displayed Only customers who have orders with items in the books table will be listed.

So customers who don't have orders would not be listed Customers who have orders but for items that are not books will NOT be listed

Fun stuff. Hope you enjoy it.

As to your second question: Right/left only matter because of the ORDER of the tables in your from statement. You could make every join a left one if you re-arrange a table order. All right/left do is specify the table from which you want ALL records.

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

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

In this case right is saying that I want all the records from the table on the right since books is last in the list you'll get all books and only those ordereditems related to a book, only those orders for which the ordered item was a book and only those customers with orders for ordered items which were books. Thus the left / right are the same except for order. I avoid right joins for readability. I find it easier to go top down when thinking about whats included and what will not be.

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

Hope this helps.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • The author of that blog repudiates it its comments. Which is good because the post is unclear & misconceived & the diagrams are unexplained & inappropriate since SQL tables are bags not sets. Although the repudiation contains more misconceptions. [Venn Diagram for Natural Join](https://stackoverflow.com/a/55642928/3404097) – philipxy Jun 23 '22 at 18:32