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!
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With