与Postgresql的性能比较:左联接vs联合全部 [英] Performance comparison with postgresql : left join vs union all

问题描述

我想知道左外部联接"和全部联合"之间最好和最快的解决方案是什么. 该数据库是PostgreSQL.

I want to know what is the best and the fastest solution between a "left outer join" and an "union all". The database is a PostgreSQL.

使用UNION ALL查询:

Query with an UNION ALL :

SELECT * FROM element, user WHERE elm_usr_id = usr_id
UNION ALL
SELECT * FROM element WHERE elm_usr_id ISNULL;

使用左外部联接进行查询:

Query with a LEFT OUTER JOIN :

SELECT * FROM element LEFT OUTER JOIN user ON elm_usr_id = usr_id;

推荐答案

您的两个查询可能不会产生相同的结果.

Your two queries may not produce the same result.

使用UNION ALL的查询将返回匹配的行以及由于elm_usr_id中的空值而导致不匹配的行.

Your query with UNION ALL returns rows that matches plus rows that not matches because of a null value in elm_usr_id.

使用LEFT JOIN(与LEFT OUTER JOIN相同)进行查询时，将返回匹配的行以及由于值不匹配而导致不匹配的行.

While the query with LEFT JOIN (same as LEFT OUTER JOIN) returns rows that matches plus rows that not matches because of any not corresponding value.

关于此，如果您希望查看所有行，则使用LEFT JOIN的查询更为安全.

Regarding to this, the query with LEFT JOIN is more secure if you expect to see all rows.

回到您的原始问题，使用LEFT JOIN的查询是利用索引的最佳选择.例如，如果您想要一个排序的结果，那么UNION查询将是最慢的.或者，如果您的查询是主查询中的子查询，则UNION将阻止对表[element]索引的任何可能利用.因此执行这样的子查询的JOIN或WHERE会很慢.

Back to your original question, the query with LEFT JOIN is the best on for taking advantage of indexes. For example, if you'd like to have a sorted result, then the UNION query will be far slowest. Or if your query is a subquery in a main query, then the UNION will prevent any possible exploitation of table [element] indexes. So it will be slow to perform a JOIN or WHERE of such a subquery.

这篇关于与Postgresql的性能比较:左联接vs联合全部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！