Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL union two tables and join with a third table

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

To do the join before the union on 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 to do the union first and then do the join:

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
like image 892
taudorf Avatar asked May 17 '11 09:05

taudorf


2 Answers

Run an EXPLAIN ANALYZE on both statements then you will see which one is more efficient.

like image 103
a_horse_with_no_name Avatar answered Oct 20 '22 09:10

a_horse_with_no_name


it can be unpredictable due to sql-engine optimizator. it's better to look at the execution plan. finally both approaches can be represented in the same way

like image 33
heximal Avatar answered Oct 20 '22 09:10

heximal