I have 2 tables

tableA:

tableB:

and I want put all rows to one result and sort it by column date

Wanted result (rows from both tables sorted by column date DESC):

I can combine tables, but tables are "squashed"...

SELECT 
    COALESCE(a.id, b.id) AS id, 
    COALESCE(a.dateA, b.dateB) AS date, 
    a.colA, 
    b.colB
FROM tableA AS a, tableB AS b
ORDER BY date DESC

CodePudding user response：

Use UNION ALL and ORDER BY. This requires enumerating the columns:

select id, dateA as dateX, colA, null as colB from tableA
union all
select id, dateB, null, colB from tableB
order by dateX

union all combines two datasets. Since we have different columns in the two tables, we need to arrange both select clauses so that they return the same set of columns ; null values can be used to fill in "missing" columns in a given dataset.

As for sorting the resultset : in MySQL, a single ORDER BY in a UNION ALL query applies to the whole resultset (after it was unioned). The query just does that (using dateX, the column alias that was created in the subqueries).