Whimusical Whimusical - 6 months ago 24
MySQL Question

Nested INNER JOIN vs INNER JOIN vs WHERE: correctness, performance, clarity for a particular case (not a typical JOIN vs WHERE issue)

I'm studying inner joins and I´m a old WHERE SQL-92 man. I'd like to know the implications and understand how it works. So this is just a theoretical question about SQL joins.
Is this...

SELECT * FROM -- Query 1
tbl1
INNER JOIN (
tbl2
INNER JOIN (
tbl3 INNER JOIN tbl4 ON tbl3.Col1 = tbl4.Col1
)
ON tbl2.col1 = tbl3.col2
)
ON tbl1.col1 = tbl3.col3


...the same as this?

SELECT * FROM -- Query 2
tbl3
INNER JOIN tbl4 ON tbl3.col1 = tbl4.col1
INNER JOIN tbl2 ON tbl2.col1 = tbl3.col2
INNER JOIN tbl1 ON tbl1.col1 = tbl3.col3


...or this (not sorted by logical resolution)?

SELECT * FROM -- Query 3
tbl3
INNER JOIN tbl1 ON tbl1.col1 = tbl3.col3
INNER JOIN tbl2 ON tbl2.col1 = tbl3.col2
INNER JOIN tbl4 ON tbl3.col1 = tbl4.col1


..or this (reference node changed; see there's a table referenced before it is cited, but the Cartesian product should be the same)

SELECT * FROM -- Query 4
tbl4
INNER JOIN tbl1 ON tbl1.col1 = tbl3.col3
INNER JOIN tbl2 ON tbl2.col1 = tbl3.col2
INNER JOIN tbl3 ON tbl4.col1 = tbl3.col1


..or this?

SELECT * FROM -- Query 5
tbl1,tbl2,tbl3,tbl4
WHERE
tbl3.col1 = tbl4.col1
tbl2.col1 = tbl3.col2
tbl1.col1 = tbl3.col3


...from aesthetic, syntactic, best practice and functional points of view?

It´s a very open question but I think is pretty interesting for the community to throw some light!

Answer

All database engines I've been closely working with (this is SQL Server, Oracle, PostgreSQL, MySQL, Sybase, SQLite, Informix and Firebird) will optimize it to the same plan.

The fourth query, however, won't parse on all engines (you cannot reference a table in an ON clause before it had been used in a JOIN clause)

MySQL offers STRAIGHT_JOIN clause which affects the join order used in the plan.

In Oracle, there is a hint /*+ ORDERED */ and in SQL Server there is a similar hint FORCE ORDER. If a query uses these hints, the plan order will be affected by the join order as well.