SQL INNER JOIN over multiple tables equal to WHERE syntax

Question

I have two PostgreSQL queries that join multiple tables:

Firstly:

SELECT iradio.id, iradio.name, iradio.url, iradio.bandwidth, genre_trans.name FROM service_iradio_table AS iradio, genre_table AS genre, genre_name_table AS genre_name, genre_name_translation_table AS genre_trans, genre_mapping_table AS genre_mapping, language_code_table AS code WHERE iradio.id=genre_mapping.s_id AND genre_mapping.g_id=genre.id AND genre.id=genre_name.g_id AND genre_name.t_id=genre_trans.id AND genre_trans.code_id=code.id AND iradio.name='MyRadio' AND code.language_iso_code='ger' 

Secondly:

 SELECT iradio.id, iradio.name, iradio.url, iradio.bandwidth, genre_trans.name FROM service_iradio_table AS iradio INNER JOIN genre_mapping_table AS genre_mapping ON iradio.id=genre_mapping.s_id INNER JOIN genre_table AS genre ON genre_mapping.g_id=genre.id INNER JOIN genre_name_table AS genre_name ON genre.id=genre_name.g_id INNER JOIN genre_name_translation_table AS genre_trans ON genre_name.t_id=genre_trans.id INNER JOIN language_code_table AS code ON genre_trans.code_id=code.id WHERE iradio.name='MyRadio' AND code.language_iso_code='ger' 

So, based on MySQL, I thought that the first query should be slower than the second because of the cross-reference to each table.

In postgreSQL, both queries seem to be internally the same. With the keyword "EXPLAIN", the output is the same for the two queries.

Question

Is it true that these queries are "equal"? Is it really a goog design to join tables this way?

At the end, this attempt to tune performance also works with the same output using "EXPLAIN":

 SELECT iradio.id, iradio.name, iradio.url, iradio.bandwidth, genre_trans.name FROM service_iradio_table AS iradio INNER JOIN genre_mapping_table AS genre_mapping ON iradio.id=genre_mapping.s_id AND iradio.name='MyRadio', genre_table AS genre, genre_name_table AS genre_name, genre_name_translation_table AS genre_trans, language_code_table AS code WHERE genre_mapping.g_id=genre.id AND genre.id=genre_name.g_id AND genre_name.t_id=genre_trans.id AND genre_trans.code_id=code.id AND code.language_iso_code='ger' 

All requests are processed within 2 ms.

Answer 1

The query planner takes all the WHERE and JOIN predicates into account (almost) the same way, trying to optimize the join order. No wonder you get the same performance. In the documentation:

The explicit syntax of the inner join ( INNER JOIN , CROSS JOIN or unadorned JOIN ) is semantically the same as listing the input relations in FROM , so it does not limit the join order.

The only difference: the explicit join syntax instructs this join order when the total number of tables is greater than the value for join_collapse_limit .

And the explicit JOIN binds before the commas in the FROM list, which has to do with valid syntax. Example.

In any case, comma-separated table lists in the FROM do not expire . This is just a good form and a lot more readable to use explicit join syntax.

Also note that OUTER joins behave differently because they cannot be reordered as freely by the query planner due to logical consequences.