Limit total number of results across tables

I want to get the most recent 100 events that happened. The events are scattered across multiple tables. Here is an example:

SELECT * FROM log_items_purchased ORDER BY timestamp DESC LIMIT 100
UNION
SELECT * FROM log_items_fulfilled ORDER BY timestamp DESC LIMIT 100
UNION
SELECT * FROM log_items_shipped ORDER BY timestamp DESC LIMIT 100

This will return up to 300 records. I would then take the result set, order by timestamp, and take the first 100 records. How can I perform this in a single SQL query, where SQL will only return 100 records in the result set.

I realize that it would be possible to do this by removing the LIMIT 100 from each query, and then make an outer query that adds LIMIT 100, but these tables are really big, and that’s really inefficient.

Answer

If you want to do this in SQL, use a subquery:

SELECT e.*
FROM ((SELECT * FROM log_items_purchased ORDER BY timestamp DESC LIMIT 100
      ) UNION ALL
      (SELECT * FROM log_items_fulfilled ORDER BY timestamp DESC LIMIT 100
      ) UNION ALL
      (SELECT * FROM log_items_shipped ORDER BY timestamp DESC LIMIT 100
      )
     ) e
ORDER BY timestamp DESC
LIMIT 100;

Note: Do not use UNION. It incurs overhead to remove duplicates.