SET OPERATORS

Set operators in sql server are used to combine the output of multiple queries.
When ever we want to combine the output of multiple queries we have to identify three factors.

1.Whether the multiple queries contains equal number of columns or not?
2.If they are equal again we have to identify whether their data types are equal or not?
3.We have to identify whether the output column name coming from first query or not?

In Sql Server there existed four types of Set Operators

1. Union all
2. Union
3. Intersect
4. Except

Union all: It combines the output of multiple queries including duplicate values.

Syntax: select column1, column2………from table1 union all select column1, column2….from table2

Example: select empno from emp union all select detpno from dept

Union: It combines the output of multiple queries without considering duplications values, mean time it arranges output data in ascending order.

Syntax: select column1, column2………from table1 union select column1, column2….from table2

Example: select empno from emp union select detpno from dept

Intersect: It selects the common values from given set of queries.

Syntax: select column1, column2………from table1 intersect select column1, column2….from
table2

Example: select empno from emp intersect select detpno from dept
Expect: It selects particular values from the first query which are not available in second query.

Syntax: select column1, column2………from table1 expect select column1, column2….from
table2

select empno from emp expect select detpno from dept

SET OPERATORS