"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea,IBM and Mphasis etc"
INTRODUCTION:
It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results.In other words,multiple select statements of similar column name in same order and having same data type to produce the unified result.In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement.
Column must be of the same datatype for these operations of one select statement with another select statements. Columns must be in the same order in this set operators in the multiple select statements. Same number of columns should be used in the multiple selecting while using these operators. It can be defined as:" It is the type of set operators which is used in the database for obtaining the result set by combining multiple tables."
The output of the query contain the same column name and order which is the first select statement column name and order.Same columns and order will be the same in the multiple select statements.The numbers of column and order should be the same in the multiple select statements and each column having same data type.
One can say that the join and union are different thing. Join are combining the data from the different tables but union is used to combine the data from the same table.Join append the table data horizontally whereas the union combines the table's data vertically.
DIFFERENCE:
|
UNION
|
UNION ALL
|
|
It keeps the
unique records of rows.
|
It keeps the
duplicate records of the rows.
|
|
Execution
time is more as it removes the duplicates.
|
Execution
time is less.
|
|
Select column_names
from table1 where conditions
Union
Select
column_names from table2 where
Conditions
|
Select
column_names from table1 where conditions
Union all
Select
column_names from table2 where
Conditions
|
|
Slow
performance is due to elimination of duplicate records.
|
Fast
performance is due to presence of duplicate records.
|
|
Performance
hit issue because load on database server to delete the duplicate rows.
|
Performance
hit issue is not observed.
|
|
It is slower
than union all.
|
It is faster
than union.
|
|
Result set is
sorted in ascending order. In other words,output is sorted by default.
|
Result set is
not sorted in ascending order. In other words,output is not sorted by default.
|
|
It is more
preferred by the database users.
|
It is less
preferred by the database users.
|
QUERY:
select * from employee
desc employee;
create table department(name varchar(10));
desc department;
insert into employee
select 'Rahul' from dual
union
select 'Rakesh' from dual
union
select 'Monkey' from dual
union
select 'Tiger' from dual
insert into department
select 'Rahul' from dual
union
select 'Rakesh' from dual
union
select 'Monkey' from dual
union
select 'Tiger' from dual
select * from employee
select * from department
select * from employee
union
select * from department
select * from employee
union all
select * from department
select * from employee e join department d
on e.name=d.name
select to_date(sysdate,'dd-mm-yyyy') from dual
union
select to_date(sysdate,'dd-mm-yyyy') from dual
select to_date(sysdate,'dd-mm-yyyy') from dual
union all
select to_date(sysdate,'dd-mm-yyyy') from dual
Comments
Post a Comment