Combing two different tables together for a query

I have two tables that share some column names but I don’t really care about that.

I just want to combine the two tables and sort by created_at date. For one of the columns I’d like to populate a default value when its coming from table b

I would take one table like this

id created title category
13 2021-01-01 “Hello” Welcome message
16 2021-01-03 “Hi” Welcome message

combine it with a table like this

id created link
13 2021-01-02 so.ca

and I’m looking for something like this

id created link title category
0 2021-01-01 null “Hello” Welcome message
1 2021-01-02 so.ca null tableB item
2 2021-01-03 null “Hi” Welcome message

How do I best go about this? I only need this table for the query. I’m using rails and mysql 5.7. Thank you!

Answer

You can use:

select (@rn := @rn + 1), created, link, title, category
from ((select ids, created, null as link, title, category
       from table1
      ) union all
      (select id, created, link, null, 'tableB item'
       from table2
      )
      order by created
     ) tt cross join
     (select @rn := 0) params