Aggregate Same Column, Different Tables

I have the following schema:

table_1

id, updated_at

table_2

id, updated_at

table_3

id, updated_at

and in total there are about 12 tables that I want to get the updated_at column and determine the MAX() aggregate value from all of them, based on a single id value.

How would I set up this query?

Get the maximum updated_at for a given id from each table, combine the results into one row set and then get the maximum from it

SELECT
  MAX(updated_at)
FROM
  (
    SELECT MAX(updated_at) AS updated_at FROM table1 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table2 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table3 WHERE id = @param_id
    UNION ALL
    ...
  ) AS derived
;

This is much more efficient then combining the tables first and then filtering and aggregating the combined set, even though the code would look simpler that way.

This method can also benefit from an index on (id, udpated_at) in each table, while probably no index would help if you decided to combine whole tables first.

StackExchange Database Administrators Q#204133, answer score: 3