select 2 table with same column without join

Question

i have 2 table like this

---------tbl1----------------
id  | name   | lname |
1      j          h
2      jj         hh

and

---------tbl2----------------
_id  | name   | lname |
1        a         b
2        aa        bb
3        aaa       ccc

i want to select from this 2 table like this:

---------result----------------
resultId   |  id  | name   | lname | 
   1           1      j         h
   2           2      jj        hh  
   3           1      a         b
   4           2      aa        bb
   5           3      aaa       ccc

but with select using join the result not this format! what can i do to access this select result?

score 3 · Accepted Answer · answered Jul 02 '17 at 11:25

You are basically looking for union all:

select row_number() over (order by which, id) as resultid,
       id, name, lname
from ((select id, name, lname, 1 as which from tbl1
      ) union all
      (select _id, name, lname, 2 as which from tbl2
      )
     ) n
order by which, id;

The only tricky part is the use of row_number() to assign the final resultid.

select 2 table with same column without join

1 Answers1