Geek asks and answers

PostgreSQL joins two tables and joins a third table

I want to join with tables and join them with a third metadata table, and I would like to know which approach is best / fast?
The database is PostgreSQL.
Below are my two suggestions, but welcome other approaches.

To perform a join before joining in both tables:

SELECT a.id, a.feature_type, b.datetime, b.file_path
FROM table1 a, metadata b WHERE a.metadata_id = b.id
UNION ALL
SELECT a.id, a.feature_type, b.datetime, b.file_path
FROM table2 a, metadata b WHERE a.metadata_id = b.id

Or, first merge and then connect:

SELECT a.id, a.feature_type, b.datetime, b.file_path
FROM
(
    SELECT id, feature_type, metadata_id FROM table1
    UNION ALL
    SELECT id, feature_type, metadata_id FROM table2
)a, metadata b
WHERE a.metadata_id = b.id
+3
source share
3 answers

Run EXPLAIN ANALYZE in both statements, after which you will see which one is more effective.

+5
source

sql-engine. . ,

+1

As far as I remember, when starting Explain, it will be shown that PostgreSQL interprets the second as the first , provided that there is no sentence group by(explicit or implicit because of unioninstead union all) in any of the subqueries.

0
source

More articles:


All Articles