I'm looking for logic behind the combination of multiple join operations in MySQL to select data from more than 2 tables

advertisements

I'm trying to wrap my head around all the different join operations in MySQL and found some very useful info already on this website when explained with venn diagrams. I'm confused, however, on how to combine the SELECT JOIN statements when I want to select data from 3 or more tables.

Does anyone have a good beginners reference for the logic behind this? Or is someone up to giving me a crash course?

Thank you


In a way, joining three tables is no more hard to grasp than joining two tables.

The JOIN operator is a binary operator in the same way that addition or multiplication are binary operators. You can "chain" them.

So just as you can use addition to sum up three or more terms:

2 + 4 + 3

You can use JOIN to join three or more tables:

SELECT ...
FROM Books
JOIN Authors ON Books.author_id = Authors.author_id
JOIN Publishers ON Books.publisher_id = Publishers.publisher_id

You can visualize what this means:

+---------+        +-------+        +------------+
| Authors |        | Books |        | Publishers |
+---------+        +-------+        +------------+
|    a    |<-------|   B   |---     |            |
|         |        |       |   |    |            |
|         |        |       |    --->|      p     |
+---------+        +-------+        +------------+

The purpose is to match up a row from Books to its related rows in Authors and Publishers, respectively.


Re your questions:

It seems weird to me that the from statement only occurs once

Does it also seem weird when you see an arithmetic expression like the following:

sqrt( 2 + 4 + 1 )

Why does the sqrt only appear once? Because it's a function being applied to the result of an internal expression.

Think of the FROM clause this way:

"I am selecting columns from the following set of joined tables. First join them, then I'll select the columns I want."

Does the order of the join statements affect the outcome?

No, the JOIN operation is commutative. That is, just like 2+4 = 4+2 in addition, joins give the same result in either direction. In fact, internally some SQL databases may choose to access the table in either order according to what makes it more efficient.

The exception is an LEFT OUTER JOIN or RIGHT OUTER JOIN, which is not commutative. The order matters.