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.
id
across several tables? Ugh!