Nested INNER JOIN vs INNER JOIN vs WHERE: correctness, performance, clarity for a particular case (not a typical JOIN vs WHERE issue)
join, ms-access, mysql, nested, sql
Solution
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.
Problem
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!