Set Operators
Set Operators combine results of two or more queries into a single result set.
There are three types of set operators:
  • UNION
  • EXCEPT
  • INTERSECT
UNION OPERATOR
UNION operator provides the ability to combine result sets from multiple queries into a single result set. When using UNION operator, all the queries must return the same number of columns, also the corresponding columns in all the queries must have compatible data type.
SYNTAX:
SELECT Column1, Column2 …
FROM Table1
UNION
SELECT Column1, Column2 …
FROM Table2
UNION operator automatically removes all the duplicate rows from the final result set. If duplicate rows needs to be preserved, ALL keyword must be added to the UNION operator.
SYNTAX:
SELECT Column1, Column2 …
FROM Table1
UNION ALL
SELECT Column1, Column2 …
FROM Table2
UNION operator does not guarantee the order of rows in the final result set, so if a specific order is desired, then ORDER BY clause must be defined in the last query. Also, column titles for the final result set comes from the first query. So if a column alias is required, then the alias needs to be defined in the SELECT clause of the first query.
EXCEPT OPERATOR
EXCEPT operator compares result set of two queries and returns distinct rows from one query which does not exist in the second query.
SYNTAX:
SELECT Column1, Column2 …
FROM Table1
EXCEPT
SELECT Column1, Column2 …
FROM Table2
Out of the two tables using EXCEPT operator, the table to the left of the operator is the dominant table. The final result set will contain all the rows from the left table which does not have a matching row in the right table. Just like UNION operator, both the queries must return the same number of columns and the corresponding columns in both the queries must have compatible data type.
INTERSECT OPERATOR
INTERSET operator compares result set of two queries and returns distinct rows which exist in both the result sets.
SYNTAX:
SELECT Column1, Column2 …
FROM Table1
INTERSECT
SELECT Column1, Column2 …
FROM Table2
The difference between INTERSECT operator and INNER JOIN operator is that if there exists NULL values in both the query results, INNER JOIN will treat NULL values in both the tables as different values and will not return NULL value in the result set, whereas INTERSECT will treat NULL values in both the tables as same value and will return the NULL value in the result set. Also, INNER JOIN does not remove duplicates, whereas INTERSECT operator removes all duplicate records from the result set. So, if a table does not contain NULL values, INTERSECT operator will return the same result as a query with DISTINCT and INNER JOIN operator.
Table: Product
ProductID ProductName Price
1 HD TV 1000
2 Play Station 400
3 MacBook 2000
4 IMAC 1500
5 IPOD 200
6 XBOX 500
7 MODEM 100
8 Nintendo 200
9 Speakers 100
10 Printer 200
11 SLR Camera 1000
12 Tablet 600
Example 1 (UNION)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductID < 5
UNION
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price > 800
Output :
ProductID ProductName Price
1 HD TV 1000
2 Play Station 400
3 MacBook 2000
4 IMAC 1500
11 SLR Camera 1000
Description :
The above query combines the results of two queries using UNION operator. As you can see, both the queries return the same number of columns and the corresponding columns in both queries have compatible data types. Since UNION operator discards duplicate data, ProductID 1,3 and 4 appear only ones in the result set.
Example 2 (UNION ALL)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductID < 5
UNION ALL
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price > 800
Output :
ProductID ProductName Price
1 HD TV 1000
2 Play Station 400
3 MacBook 2000
4 IMAC 1500
1 HD TV 1000
3 MacBook 2000
4 IMAC 1500
11 SLR Camera 1000
Description :
Similar to UNION clause, UNION ALL combines the results of two queries, but does not discard duplicate records. As you can see, ProductID 1,3 and 4 are duplicated in the result set.
Example 3 (EXCEPT)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductID < 5
EXCEPT
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price > 800
Output :
ProductID ProductName Price
2 Play Station 400
Description :
Here the first query is compared with the second query, and all the rows from the first query which does not exist in the second query are returned back. Just like UNION operator, all duplicate values are discarded.
Example 4 (INTERSECT)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductID < 5
INTERSECT
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price > 800
Output :
ProductID ProductName Price
1 HD TV 1000
3 MacBook 2000
4 IMAC 1500
Description :
Here the first query is compared with the second query, and all the rows from the first query which also exists in the second query are returned back. INTERSECT operator is similar to JOIN operator; except JOIN returns duplicate values, whereas INTERSECT discards duplicate values.