Sunday, May 9, 2010

The Differance Between Union and UnionALL operators

The UNION operator builds a relation from tuples appearing in either or both of the specified relations.
The UNION command is used to select related information from two tables. Much like the JOIN command.
UNION provides only distinct values as output.
 To be UNION compatible, the two tables should have the same types of attributes (Sets of columns having the same data types.).
 The UNION operator removes duplicate rows from the result set.


        UNION ALL operator all rows are included in the results and duplicates are not removed. UNION ALL selects all values and not just distinct ones.

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2]

Query of UNION ALL operator.

create database Employee_Details

create table Emp_tab1
(
[Emp name] varchar(30),
[Emp D-O-J] varchar(30),
[Emp Salary] bigint,
)
Insert into Emp_tab1 values ( 'Raj kumar' ,'15-12-2005',32000)
Insert into Emp_tab1 values ( 'Rohit singh' ,'15-12-2005',65000)
Insert into Emp_tab1 values ( 'Rikesh jha' ,'10-12-1999',5000)
Insert into Emp_tab1 values ( 'Monika panday' ,'02-05-2009',70000)
Insert into Emp_tab1 values ( 'Raj kumar' ,'15-12-2005',32000)
Insert into Emp_tab1 values ( 'Rikesh jha' ,'10-12-1999',5000)
Insert into Emp_tab1 values ( 'Vikas singh' ,'01-05-2008',85000)

create table Emp_tab2
(
[Emp name] varchar(30),
[Emp D-O-J] varchar(30),

)
Insert into Emp_tab2 values ( 'Raj kumar' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rohit singh' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rikesh jha' ,'10-12-1999')
Insert into Emp_tab2 values ( 'Monika panday' ,'02-05-2009')
Insert into Emp_tab2 values ( 'Raj kumar' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rikesh jha' ,'10-12-1999') 

command in UNION ALL query. 

select [Emp name],[Emp D-O-J]  from Emp_tab1
union all
select [Emp name],[Emp D-O-J] from Emp_tab2

The result is displayed below with duplicate records. 


The twoSet in the vain diagram shows table name Emp_tab1 and Emp_tab2.

When the UNION ALL condition is applied.


Resultant Set After the Union all condition is applied.
 



 And the final result.  ..Refer from the Query example above.


 The syntax for UNION is as follows:
[SQL Statement 1]
UNION
[SQL Statement 2]

Query of UNION operator.

create database Employee_Details

create table Emp_tab1
(
[Emp name] varchar(30),
[Emp D-O-J] varchar(30),
[Emp Salary] bigint,
)
Insert into Emp_tab1 values ( 'Raj kumar' ,'15-12-2005',32000)
Insert into Emp_tab1 values ( 'Rohit singh' ,'15-12-2005',65000)
Insert into Emp_tab1 values ( 'Rikesh jha' ,'10-12-1999',5000)
Insert into Emp_tab1 values ( 'Monika panday' ,'02-05-2009',70000)
Insert into Emp_tab1 values ( 'Raj kumar' ,'15-12-2005',32000)
Insert into Emp_tab1 values ( 'Rikesh jha' ,'10-12-1999',5000)
Insert into Emp_tab1 values ( 'Vikas singh' ,'01-05-2008',85000)

create table Emp_tab2
(
[Emp name] varchar(30),
[Emp D-O-J] varchar(30),

)
Insert into Emp_tab2 values ( 'Raj kumar' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rohit singh' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rikesh jha' ,'10-12-1999')
Insert into Emp_tab2 values ( 'Monika panday' ,'02-05-2009')
Insert into Emp_tab2 values ( 'Raj kumar' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rikesh jha' ,'10-12-1999')

command in UNION query. 

select [Emp name],[Emp D-O-J]  from Emp_tab1
union
select [Emp name],[Emp D-O-J] from Emp_tab2

The result is displayed below without duplicate records. 


 The two Set in the vain diagram shows  contents of table  Emp_tab1 and Emp_tab2.


When the  UNION conditions get applied.

Resultant Set After the Union condition is applied.



And the final results.

1 comment:

Anonymous said...

Nice Post

Recent Posts