10

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
Chau
  • 5,540
  • 9
  • 65
  • 95
taudorf
  • 783
  • 3
  • 9
  • 27
  • I find the second easier on the (human) eye. I usually opt for the most 'relationally sound' approach or the easiest to read and maintain (often the same thing, which is no mere coincidence) unless it performs really badly against another candidate construct. – onedaywhen May 17 '11 at 12:55
  • the first method works for me to get the data. how do you then insert it into `table2`? – mga Feb 10 '20 at 07:15
  • i added an `insert into (columns)` before the select and was able to add the rows – mga Feb 10 '20 at 23:41

3 Answers3

8

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

1

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

heximal
  • 10,327
  • 5
  • 46
  • 69
0

In so far as I can remember, running Explain will reveal that PostgreSQL interprets the second as the first provided that there is no group by clause (explicit, or implicit due to union instead of union all) in any of the subqueries.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154