@WikiNewPageEditViewToolsHelp
Create New Page Create New Page from Copy
Create your new wiki
Edit this page Copy from this page Rename
Attach (Upload) File
Edit Menu
Newest Change History Referer Trackback
Page List Tag Cloud RSS1.0 RSS2.0
Search
@Wiki Guide
FAQ/about @wiki FAQ/about Editting FAQ/about Register
Update Infomation Release Plan
 
SQL UNION, SQL INTERSECT, SQL EXCEPT, SQL EXISTS and SQL CASE
Table1  
 
C1
C2
1
A
B
2
A
C
3
A
D
Table2
 
C1
C2
1
A
B
2
A
C
3
A
E
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
 
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
E
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:
 
C1
C2
1
A
B
2
A
C
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:
 
C1
C2