Set Operators in SQL Server

Set operators are used to combine results from two or more SELECT statements. They combine same type of data from two or more tables. This looks similar to SQL joins although there is difference. SQL joins are used to combine columns whereas Set operators are used to join rows from different SELECT queries. They return only one result set.

      These operators work on complete rows of the queries, so the results of the queries must have same column name, same column order and the types of columns must be compatible. 

There 4 set operators in SQL Server as below

1. UNION

2. UNION ALL

3. INTERSECT

4. EXCEPT

Consider the following two tables for examples.

Table: School1

Standard	Students
First	50
Second	60
Third	40
Fifth	45
Sixth	58
Seventh	77

Table: School2

Standard	Students
First	30
Second	46
Fourth	56
Eight	46
Sixth	34

1. UNION Operator:

         UNION operator combines two or more result sets into single result set, without duplication. The union of two queries gives rows that are present first result set or in second result set or in both. But each row appears only once.

Venn diagram for UNION:                                                                                                                                                                                                                                                                                                                                     

                                                                                                                                 Example:

        SELECT Standard FROM School1

       UNION 

       SELECT Standard FROM School2

Output:

Standard

Eight

Fifth

First

Fourth

Second

Seventh

Sixth

Third

2. UNION ALL Operators:

         Like UNION operator UNION ALL operator also combines two or more result set into single result set. Only difference between UNION and UNION ALL is that UNION ALL allows duplicate rows.

Venn diagram for UNION ALL:

Example:

    SELECT Standard FROM School
    UNION ALL

    SELECT Standard FROM School2

Output:                                                                                                         

Standard

First

Second

Third

Fifth

Sixth

Seventh

First

Second

Fourth

Eight

Sixth

3. INTERSECT Operator:

                INTERSECT operator returns only those rows those are present in all the result sets. The intersection of two queries gives the rows that are present in both the result sets. It returns only unique rows.

Venn diagram for INTERSECT:

Example:

    SELECT Standard FROM School
    INTERSECT

    SELECT Standard FROM School2

Output:

Standard

First

Second

Sixth

4. EXCEPT Operator:

                EXCEPT operator returns all distinct the rows that are present in result set of first query, but not in result set of second query. It means it returns difference between two result sets.

Venn diagram for INTERSECT:

Example:

SELECT Standard FROM School1

EXCEPT

SELECT Standard FROM School2

Output:

Standard

Fifth

Seventh

Third