Union Operator

The UNION operator is used to combine the result-set of two or more SELECT statements. Union Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

There are basic rules for combining the result sets of two queries by using UNION:

·         The number and the order of the columns must be the same in all queries.

·         The data types must be compatible.

Syntax:

select column_name from table_name1
union
select column_name from table_name2

Union All Operator

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Intersect Operator

INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator.When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.

Syntax:

select column_name from table_name1
intersect
select column_name from table_name2

Except Operator
It returns all of the distinct rows from the left side of the EXCEPT operator. It also removes all of the rows from the result set that match the rows that are on the right side of the EXCEPT operator. It is same as minus operator in sql.

For Example

SELECT studentName FROM studentRecord WHERE per < 60

EXCEPT

SELECT studentName FROM studentRecord WHERE per > 60

ORDER BY studentName;