How to join two tables sorting two unique colums?

Question

I have two tables illustrated below


TABLE A                                TABLE B
-----------------------------------    -------------------------------
|id|age|status|created_at         |    |id|height|created_at         |
-----------------------------------    -------------------------------
|1 |24 | 1    |2019-09-20 02:24:09|    |2 |81    |2019-09-20 02:20:15|
|2 |45 | 1    |2019-09-20 02:02:19|    |3 |88    |2019-09-20 02:20:50|
|3 |27 | 1    |2019-09-20 02:10:29|    |4 |83    |2019-09-20 02:34:00|
|4 |31 | 0    |2019-09-20 02:04:59|    |5 |85    |2019-09-20 02:04:49|

And wish to join the two tables together to produce a single table sorted by created_at in the ASC order and id not 5

So basically i want something like this

TABLE C                               
------------------------------------------
|id|age|status|height|created_at         |
------------------------------------------
|2 |45 | 1    |      |2019-09-20 02:02:19|
|4 |31 | 0    |      |2019-09-20 02:04:59|
|3 |27 | 1    |      |2019-09-20 02:10:29|
|2 |   |      |81    |2019-09-20 02:20:15|
|3 |   |      |88    |2019-09-20 02:20:50|
|1 |24 | 1    |      |2019-09-20 02:24:09|
|4 |   |      |85    |2019-09-20 02:20:50|

@Strawberry this is not Full Outer Join problem. A possible dupe instead is this: https://stackoverflow.com/q/21493530/2469308 but then it is also not a one to one match — Madhur Bhaiya, Oct 08 '19 at 11:24

score 1 · Accepted Answer · answered Oct 08 '19 at 11:18

You basically need to do UNION ALL the two dissimilar tables and then sort them up. Also, in this particular case, you don't really need a subquery:

Query

(SELECT id, age, status, NULL height, created_at 
 FROM tableA 
 WHERE id <> 5)
UNION ALL
(SELECT id, NULL, NULL, height, created_at 
 FROM tableB
 WHERE id <> 5)
-- to sort the unionized resultset
ORDER BY created_at ASC;

Result

| id  | age | status | height | created_at          |
| --- | --- | ------ | ------ | ------------------- |
| 2   | 45  | 1      |        | 2019-09-20 02:02:19 |
| 4   | 31  | 0      |        | 2019-09-20 02:04:59 |
| 3   | 27  | 1      |        | 2019-09-20 02:10:29 |
| 2   |     |        | 81     | 2019-09-20 02:20:15 |
| 3   |     |        | 88     | 2019-09-20 02:20:50 |
| 1   | 24  | 1      |        | 2019-09-20 02:24:09 |
| 4   |     |        | 83     | 2019-09-20 02:34:00 |

View on DB Fiddle

score 0 · Answer 2 · answered Oct 08 '19 at 11:16

You can achieve this using UNION which will join the two result sets together:

SELECT *
  FROM (
        SELECT id,
               age,
               status,
               NULL AS height,
               created_at
          FROM A
        UNION ALL
        SELECT id,
               NULL AS age,
               NULL AS status,
               height,
               created_at
          FROM B
        ) a
  WHERE id != 5
  ORDER BY created_at ASC

By wrapping the results in a subquery, you can easily then filter out any that have an id = 5.

A working fiddle showing the results is here.

How to join two tables sorting two unique colums?

2 Answers2