MySQL order by time on two different tables

Question

Consider I have the following two tables (rough sketches):

Table 1:

• t1Id (Int, Primary Key)
• time (Datetime)
• attribute1
• attribute2
• attribute3

Table 2:

• t2Id (Int, Primary Key)
• time (Datetime)
• attribute4
• attribute5

Suppose I want to select results from both tables and order them by time, is this doable? I myself was thinking it would be possible, if:

• You could create an union with all columns of both tables, but time being shared over the union.
• NULL values in places where this is no match. For example a row of Table 1 in this union would have NULL in t2Id, attribute4 and attribute5.
• An extra column indicating from which table the original rows (the ones without NULL values) originated from.

How would I design such a query in the most efficient way?

Answer 1

SELECT * FROM (
   SELECT t1Id,time , attribute1,attribute2,attribute3 FROM table1 
   UNION ALL
   SELECT t2Id ,time , attribute3,attribute4,"" FROM table2  
)AA
ORDER BY AA.time