What is the sql for multiple select statements with identical fields being merged into a single output?

Question

I have three tables

table_1_Top_Performers:
col_1_ID | col_2_FirstName | col_3_LastName    
1          Johny               Smith
2          John                Que
3          Kristina            Zimmerman

table_2_Mid_Performers:
col_1_ID | col_2_FirstName | col_3_LastName
1          Stewart            Joe
2          Amy                Green
3          John               Smithsonian

table_3_Bottom_Performers:
col_1_ID | col_2_FirstName | col_3_LastName    
1          Noah              Cantana
2          Abe               Jackson
3          Smith             Markson

And I the output I'm looking for is this:

col_1_ID | col_2_FirstName | col_3_LastName    
1          Noah              Cantana
2          Abe               Jackson
3          Smith             Markson
1          Stewart           Joe
2          Amy               Green
3          John              Smithsonian
1          Johny             Smith
2          John              Que
3          Kristina          Zimmerman

Could these be achieved by a simple SQL statement? or even a PL/SQL call?

lookup `UNION ALL` or `UNION` for distinct values – JamieD77 Jul 24 '15 at 22:21 — JamieD77, Jul 24 '15 at 22:21

score 1 · Accepted Answer · edited Jul 24 '15 at 23:20

You can generate your output with UNION ALL

create table top_performers (id int,firstname nvarchar(30),lastname nvarchar(30));    
insert into top_performers(id,firstname,lastname)
values (1,'Johny','Smith'),(2,'John','Que'),(3,'Kristina','Zimmerman');

create table mid_performers (id int,firstname nvarchar(30),lastname nvarchar(30));    
insert into mid_performers(id,firstname,lastname)
values (1,'Stewart','Joe'),(2,'Amy','Green'),(3,'John','Smith');

create table bottom_performers (id int,firstname nvarchar(30),lastname nvarchar(30));
insert into bottom_performers(id,firstname,lastname)
values (1,'Noah','Cantana'),(2,'Abe','Jackson'),(3,'Smith','Markson');

select *,'top' from top_performers
union all
select *,'mid' from mid_performers
union all 
select *,'bottom' from bottom_performers

OUTPUT:

id  firstname   lastname    (No column name)    
1   Johny       Smith        top    
2   John        Que          top    
3   Kristina    Zimmerman    top    
1   Stewart     Joe          mid    
2   Amy         Green        mid   
3   John        Smith        mid   
1   Noah        Cantana      bottom    
2   Abe         Jackson      bottom
3   Smith       Markson      bottom

I did some minor formatting to make it more readable, hope you don't mind. — jpw, Jul 24 '15 at 23:20

What is the sql for multiple select statements with identical fields being merged into a single output?

1 Answers1