set operators in sql server

Union:

It is used to combine the data from two sets.

Example:

display the list of all jobs available in dept 20 and 30

select job from emp where deptno=20

union

select job from emp where DEPTNO=30

with the union set operator you can use the keyword “ALL” to get the output of “UNION” including duplicates.

Example:

select job from emp where deptno=20

union all

select job from emp where DEPTNO=30

Intersect:

It will return only the values that are common in two sets.

Example:

Display list of jobs that are common in the department 20 and 30

select job from emp where deptno=20

intersect

select job from emp where DEPTNO=30

except:

It will return the values that are available in first set but not in second set.

Example:

Display the list of  jobs that are available in dept no 20 , but not in 30

select job from emp where deptno=20

except

select job from emp where DEPTNO=30

Rules to follow:

No of columns selected in all select statement on which you apply set operator must be same.

Data type of corresponding columns in all select statement on which you are applying set operators must be same.

Example:

Find the jobs that are available in dept no  20 and dept no 30 but no in dept no 10

select job from emp where deptno=20

union

select job from emp where DEPTNO=30

except

select job from emp where DEPTNO=10

Find the languages in which no software was developed.

select prof1 from PROGRAMMER

union

select prof1 from PROGRAMMER

except

select developin from SOFTWARE

UNION	JOIN
Accumulates the data	Relates the data
To dissimilar structure can’t be combined with UNION operator	To dissimilar structure can be joined.