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

Question

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!

Solution 1

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.

Solution 2

From a correctness standpoint they are likely equal. From a performance standpoint, it depends on the RDBMS (I can only speak for SQL Server, where all variants will yield the same execution plan).

Solution 3

In Sybase they will have the same execution plan as well! The query optimizer is smart enought to build the same execution plan for all of them :)

He might change internally the query to select first the smallest table and make the inner joins with that one.

Regardind using the inner join or the nested join (the last one), I prefer the first one. It's more readable to have the inner joins with the connections between tables and afterwards, if needed, a where clause with the restrictions (in your example you don't have these restrictions).