SET operators are mainly used to combine the same type of data from two or more tables. Although more than one select statement will then be present, only one result set is returned.
Rules on Set Operations:
- The result sets of all queries must have the same number of columns.
- In every result set the data type of each column must match the data type of its corresponding column in the first result set.
- In order to sort the result, an ORDER BY clause should be part of the last statement.
- The records from the top query must match the positional ordering of the records from the bottom query.
- The column names or aliases must be found out by the first select statement.
Four Set Operators:
The four set operators union, union all, intersect and except allow us to serially combine two or more select statements.
| Operator |
Returns |
| UNION |
Combine two or more result sets into a single set, without duplicates. |
| UNION ALL |
Combine two or more result sets into a single set, including all duplicates. |
| INTERSECT |
Takes the data from both result sets which are in common. |
| EXCEPT |
Takes the data from first result set, but not the second (i.e. no matching to each other) |
SYNTAX
For set operators, the syntax is simple.
SELECT [Column_Name, . . . ] FROM [table1] [set operator]
SELECT [Column_Namse, . . .] FROM [table2] [set operator]
...
...
SELECT [Column_Name, . . . ] FROM [tableN]
Example
Create two tables with same column name and data type.
CREATE TABLE Students2000(
Name VARCHAR(15),
TotalMark INT)
CREATE TABLE Stundents2005(
Name VARCHAR(15),
TotalMark INT)
Let us insert a few values into the tables.
INSERT INTO Students2000 VALUES('Robert',1063);
INSERT INTO Students2000 VALUES('John',1070);
INSERT INTO Students2000 VALUES('Rose',1032);
INSERT INTO Students2000 VALUES('Abel',1002);
INSERT INTO Students2005 VALUES('Robert',1063);
INSERT INTO Students2005 VALUES('Rose',1032);
INSERT INTO Students2005 VALUES('Boss',1086);
INSERT INTO Students2005 VALUES('Marry',1034);
Result
Result Set for Students2000 table
Result Set for Students2005 table
UNION ALL
The SQL UNION ALL Operator is used to list all records from two or more select statements. All the records from both tables must be in the same order.
SELECT Name,TotalMarks FROM students2000 UNION ALL
SELECT Name,TotalMarks FROM students2005
Result
Here Robert and Rose are stored in both tables. UNION ALL retuns all records (including duplicate records).
UNION
The SQL Union ALL Operator is used to combine two tables using select statement when both tables have the same number of columns.
Union works like Distinct. Union all DOES NOT do distinct.
SELECT Name,TotalMarks FROM students2000 UNION
SELECT Name,TotalMarks FROM students2005
Result
The Robert and Rose records are duplicate records. Thus, these are returned only once.
INTERSECT
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
SELECT Name,TotalMarks FROM students2000 INTERSECT
SELECT Name,TotalMarks FROM students2005
Result
Only the Robert and Rose records are returned, because they are found in both tables.
EXCEPT
EXCEPT clause in SQL Server is working as like MINUS operation in Oracle. EXCEPT query returns all rows which are in the first query but those are not returned in the second query.
Example 1
SELECT Name,TotalMarks FROM students2000 EXCEPT
SELECT Name,TotalMarks FROM students2005
Example 2
EXCEPT returns any distinct values from the left select query that are not also found on the right select query.
SELECT Name,TotalMarks FROM students2005 EXCEPT
SELECT Name,TotalMarks FROM students2000
From the two results we understand that if any records are found in both tables, they are removed from the first table's record set.
The four set operators (union, union all, intersect and except) in SQL all have the same precedence.