postgresql - SQL INNER JOIN over multiple tables equal to WHERE syntax

Question

I have two PostgreSQL queries which connect multiple tables:

First:

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'

Second:

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 coming from MySQL I thought that the first query must be slower than the second one because of cross referencing each table.

It seems that in postgreSQL both queries are internally the same. With keyword "EXPLAIN" for the two queries the output is the same.

Question

Is it really true that these queries are "equal"? At all is it a goog design to join tables in such a way?

At the end also this try of performance tuning is running into the same output with "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 queries are processed within 2ms.

Answer 1

The query planner takes all WHERE and JOIN predicates into consideration in (almost) the same way, when trying to optimize join order. It's no surprise you get the same performance. Per documentation:

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

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

And explicit JOINs bind before commas in the FROM list, which is relevant to permissible syntax. Example.

In any case, comma-separated lists of tables in the FROM clause are in no way deprecated. It's just good form and much more readable to use explicit join syntax.

Also note that OUTER joins behave differently in that they cannot be rearranged as freely by the query planner due to logic implications.