ARTICLE
SQL UNION, SQL INTERSECT, SQL EXCEPT, SQL EXISTS and SQL CASE for beginners
The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables.
Table1
Table2
SQL UNION
The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to
JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need
to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).
The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL
and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.
The syntax is as follows
[SQL Statement 1]
UNION [ALL]
[SQL Statement 2]
The following 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.
SELECT C1,C2 FROM T1
UNION
SELECT C1,C2 FROM T2
Result
| C1 | C2 |
| 1 | A | B |
| 2 | A | C |
| 3 | A | D |
| 4 | A | E |
SELECT C1,C2 FROM T1
UNION ALL
SELECT C1,C2 FROM T2
Result
| C1 | C2 |
| 1 | A | B |
| 2 | A | C |
| 3 | A | D |
| 4 | A | B |
| 5 | A | C |
| 6 | A |
E |
SQL INTERSECT
Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an
OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator
(value is selected only if it appears in both statements). Returns distinct values by comparing the results of two queries.
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use INTERSECT are the following
-
The number and the order of the columns must be the same in all queries.
-
The data types must be compatible.
The syntax is as follows
[SQL Statement 1]
INTERSECT
[SQL Statement 2]
SELECT C1,C2 FROM T1
INTERSECT
SELECT C1,C2 FROM T2
Result
Note: INTERSECT will work only in SQL 2005.
SQL EXCEPT
The EXCEPT (MINUS) operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones
that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first
SQL statement, such results are ignored.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
The basic rules for combining the result sets of two queries that use EXCEPT are the following
-
The number and the order of the columns must be the same in all queries.
-
The data types must be compatible.
The syntax is as follows
[SQL Statement 1]
EXCEPT
[SQL Statement 2]
SELECT C1,C2 FROM T1
EXCEPT
SELECT C1,C2 FROM T2
Result