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?
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.