mysql - Combining results of select queries without using set operators(UNION) -

is possible without using union operator combining results of queries in sql (which works same union/union all)

let's suppose have 2 tables want union

create table table1 (`id` int, `name` varchar(32)); create table table2 (`id` int, `name` varchar(32)); 

and sample data

table1:

 | id |  name | |----|-------| |  1 | name1 | |  2 | name2 | |  3 | name3 | 

table2:

 | id |  name  | |----|--------| | 11 | name11 | | 22 | name22 | | 33 | name33 | |  1 |  name1 | 

to emulate union all

select coalesce(t1.id, t2.id) id,        coalesce(t1.name, t2.name) name    (   select table_name <> 'table1' n     information_schema.tables    table_schema = schema()      , table_name in('table1', 'table2') ) t left join table1 t1      on t.n = 0 left join table2 t2     on t.n = 1  

output:

 | id |   name | |----|--------| |  1 |  name1 | |  2 |  name2 | |  3 |  name3 | | 11 | name11 | | 22 | name22 | | 33 | name33 | |  1 |  name1 | 

to emulate union need add distinct

select distinct coalesce(t1.id, t2.id) id,        coalesce(t1.name, t2.name) name    (   select table_name <> 'table1' n     information_schema.tables    table_schema = schema()      , table_name in('table1', 'table2') ) t left join table1 t1      on t.n = 0 left join table2 t2     on t.n = 1  

output:

 | id |   name | |----|--------| |  1 |  name1 | |  2 |  name2 | |  3 |  name3 | | 11 | name11 | | 22 | name22 | | 33 | name33 | 

here sqlfiddle demo