Sunday, May 9, 2010

Understanding UNION in SQL Server

The Union operator builds a relation from tuples appearing in either or both of the specific relations.
         To be Union compatible, the two tables should have the same types of attributes (sets of columns having the same data types)
{ | ( ) }
      UNION [ALL]
| ( )
[ UNION [ ALL ]  | ( )
   [ …n ] ]

Query of UNION operator.

(--Create a table )

create table Emp_tab1
[Employee Name] varchar(50),
[Employee Dept] varchar(20),
[Employee Salary] bigint,
[Employee city] varchar(50),
[Employee contact#] bigint,
[Employee ID] int,

(--insert value in table)

insert into Emp_tab1 values('johan', 'Devloper', 25324, 'Mumbai',2487563,1)
insert into Emp_tab1 values('James bond', 'Testing', 26314, 'New york',2546823,2)
insert into Emp_tab1 values('Harry', 'HR', 10253, 'New Delhi', 2215896,3)
insert into Emp_tab1 values('Michel', 'clerck', 8000, 'pune',2245614,4)
insert into Emp_tab1 values('Thomes', 'Manager', 52364, 'Banglor',2325642,5)
insert into Emp_tab1 values('jacky', 'Project manager', 524682, 'New york',3225645,6)

(--Create a table)

create table Emp_Details
[Employee id] int,
[Employee skill] varchar(50),
[Employee postal add] varchar(30),
[Employee E-mail ID] varchar(30),
[Employee home ph no] bigint,
[Employee D-O-B] varchar(30)

(--insert value in table)

insert into Emp_Details values(1,'C++','2536822','',253645,'15-10-75')
insert into Emp_Details values(2,'C#,Java','5865822','',253568,'05-01-78')
insert into Emp_Details values(3,'C++,DBMS','2256846','',255689,'11-12-85')
insert into Emp_Details values(4,'C,C++,.NET','2854822','',2548925,'25-01-86')
insert into Emp_Details values(5,',sql','2525612','',2525625,'01-05-95')
insert into Emp_Details values(6,'oct','225982','',24585875,'15-10-85')

command in union operator query . 

select  [Employee skill],[Employee E-mail ID],[ Employee home ph no],[Employee D-O-B] from Emp_Details
select [Employee Name],[Employee Dept],[Employee Salary],[Employee city] from Emp_tab1

NOTE:- Two table create Emp_Details and Emp_tab1 and Database name Employee Record, Emp_Details contains the Employee id, Employee skill, Employee postal add, Employee E-mail id, Employee home ph no, Employee D-O-B and Emp_tab1 contains the Employee Name,Employee Dept, Employee Salary, Employee city, Employee contact#, Employee id. These tables are union compatible because the have the same types of attributes.

The results are displayed below.

How does the Union get Applied
 Two table sets shows the value in Emp_Details and Emp_tab1.
This diagram shows the result after union operator.

This diagram shows the  final result format.

No comments:

Recent Posts