Set Operators in SQL SERVER

Set operators

Set Operators are used to join the results of two or more queries(two or more select statements).

Set operators are used to join the records of two different tables where are as joins are used to join the columns of different tables.

To combine the results of  two queries we need to follow the below basic rules

1.The number and teh order of the columns must be same in all the queries.
2.The datatypes must be compatible.

set operators are as follows:

1.union
2.union all
3.intersect
4.except

create table course1
(
coursename varchar(15),
)
insert into course1 values ('C++'),
('C#.net'),('asp.net'),('oracle'),('C')

create table course2
(
coursename varchar(15),
)
insert into course2 values ('vC++'),
('C#.net'),('asp.net'),('php'),('java')

1.UNION:

Combines the results of two or moew queries into a single result set,that includes all the rows that belong to all the queries in the union but it will return only distinct values.

Examples:

select Coursename from course1
UNION
select Coursename from course2

select Job from Employee10 where dno=10
UNION
select job from Employee10 where dno=20

select eno,ename,Job from Employee10 where dno=10
UNION

select dno,ename,job from Employee10 where dno=20

2.UNION ALL

It is same as UNION but it will return duplicate values also.

Examples:

select Coursename from course1
UNION ALL
select Coursename from course2

select Job from Employee10 where dno=10
UNION ALL
select job from Employee10 where dno=20

3.INTERSECT
Returns the common values from both of the result sets but here also it will return the distinct values.

Examples:
select Coursename from course1
INTERSECT
select Coursename from course2

select Job from Employee10 where dno=10
INTERSECT
select job from Employee10 where dno=20

4.EXCEPT

Here we will get the distinct values from left side table which are not available on right side table.

Examples:
select Coursename from course1
EXCEPT
select Coursename from course2

select eno,ename,Job from Employee10 where dno=10
EXCEPT
select dno,ename,job from Employee10 where dno=20

Let me know if you need any more information on this Article.

Thanks
Srinivas