Union and Union all. Session 15

Union and Union ALL Union Union is used to combine two queries into a single result set using the select statements and it does not allow duplicate values. Union All: Union All is used to combine two queries into a single result set using the select statements and it allow duplicate values Order by clause should be used only in the last statement in the union query Differences between Union and Union All • UNION only returns unique and removes duplicate data • UNION ALL returns all records, including duplicates. • Union had to make distinct sort to remove duplicates so it is less fast than union all Differences between Union and Join Union combines rows from two or more table whereas join combines columns from two or more tables ------------------------------------------------------------------------------------- create table a ( id int, name varchar(20), salary int, ) insert into a values(1,'sunny', 1000) insert into a values(2,'nisha',2000) insert into a values(1,'yogesh',3000) delete from a where name='yogesh' create table b ( id int, name varchar(20), salary int ) insert into b values(1,'yogesh',3000) insert into b values(2,'ambika',4000) create table c ( name varchar(20), id int, salary int, ) create table d ( name varchar(20), id int, salary int, section varchar(20) ) insert into d values('yogesh',1,3000,'a') insert into d values('ambika',2,4000,'b') ------------------------------------------------------------------------------------- select * from a select * from b select * from c select * from d insert into b values(1,'sunny',1000) select * from a join b on a.id=b.id select * from a union all select * from b select * from a union select * from b