在多个表上的SQL INNER JOIN等于WHERE语法 [英] SQL INNER JOIN over multiple tables equal to WHERE syntax

问题描述

我有两个连接多个表的PostgreSQL查询:

I have two PostgreSQL queries which connect multiple tables:

第一:

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'

第二:

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'

因此来自MySQL，我认为由于交叉引用每个表，因此第一个查询的速度必须慢于第二个查询.

So coming from MySQL I thought that the first query must be slower than the second one because of cross referencing each table.

似乎在postgreSQL中，这两个查询在内部是相同的.使用关键字"EXPLAIN"的两个查询的输出是相同的.

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

问题

这些查询是否真的是相等的"?完全以这种方式联接表是goog设计吗?

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

最后，这种性能调整尝试也与"EXPLAIN"运行到相同的输出中:

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'

所有查询均在2毫秒内处理完毕.

All queries are processed within 2ms.

推荐答案

在尝试优化联接顺序时，查询计划器以几乎相同的方式将所有WHERE和JOIN谓词考虑在内.获得相同的性能也就不足为奇了. 每个文档:

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:

显式内部联接语法(INNER JOIN，CROSS JOIN或未修饰的JOIN) 在语义上与在FROM中列出输入关系相同，因此 不限制加入顺序.

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.

和显式JOIN绑定在FROM列表中的逗号之前，这与允许的语法有关. 示例.

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

在任何情况下，FROM子句中以逗号分隔的表列表都是不建议使用的.使用显式联接语法只是一种好的形式，并且更具可读性.

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.

还请注意，OUTER联接的行为有所不同，因为由于逻辑上的原因，查询计划者无法随意重新排列它们.

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

这篇关于在多个表上的SQL INNER JOIN等于WHERE语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！