Set operators are used to combine results from two or more SELECT statements.
Set operators are used to combine results from two or more SELECT statements. They combine the same type of data from two or more tables. This looks similar to SQL joins although there is a difference. SQL joins are used to combine columns whereas Set operators are used to join rows from multiple 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 the same column name, same column order and the types of columns must be compatible. There are the following 4 set operators in SQL Server:
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
30
46
Fourth
56
Eight
34
1. UNION Operator
The UNION operator combines two or more result sets into a single result set, without duplications. The union of two queries gives rows that are present in the first result set or in the second result set or in both. But each row appears only once.
Output
2. UNION ALL OperatorsLike the UNION operator the UNION ALL operator also combines two or more result sets into a single result set. The only difference between a UNION and UNION ALL is that the UNION ALL allows duplicate rows.Venn diagram for UNION ALL:
Example
4. EXCEPT OperatorThe EXCEPT operator returns all distinct the rows that are present in the result set of the first query, but not in the result set of the second query. It means it returns the difference between the two result sets.Venn diagram for INTERSECT:
Building Standalone Data-Driven Applications using SQL Server Compact 4.0