Sams Teach Yourself SQL in 24 Hours

Recommended Book 

Compound Query Operators

The compound query operators vary among database vendors. The ANSI standard includes the UNION, UNION ALL, EXCEPT, and INTERSECT operators, all of which are discussed in the following sections.

The UNION Operator

The UNION operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. In other words, if a row of output exists in the results of one query, the same row is not returned, even though it exists in the second query that combined with a UNION operator. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order—but they do not have to be the same length.

The syntax is as follows:

   
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
UNION
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]

Look at the following example:

   
SELECT EMP_ID FROM EMPLOYEE_TBL
UNION
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL;

Those employee IDs that are in both tables appear only once in the results.

This hour's examples begin with a simple SELECT from two tables:

   

   SELECT PROD_DESC FROM PRODUCTS_TBL;

   
PROD_DESC
-----------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF

11 rows selected.


   SELECT PROD_DESC FROM PRODUCTS_TMP;

   
PROD_DESC
--------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF


11 rows selected.

Now, combine the same two queries with the UNION operator, making a compound query.

   
   

   SELECT PROD_DESC FROM PRODUCTS_TBL

   UNION

   SELECT PROD_DESC FROM PRODUCTS_TMP;

   
PROD_DESC
-----------------------
ASSORTED COSTUMES
ASSORTED MASKS
CANDY CORN
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY
WITCHES COSTUME
KEY CHAIN
OAK BOOKSHELF

11 rows selected.

In the first query, nine rows of data were returned, and six rows of data were returned from the second query. Nine rows of data are returned when the UNION operator combines the two queries. Only nine rows are returned because duplicate rows of data are not returned when using the UNION operator.

The next example shows an example of combining two unrelated queries with the UNION operator:

   
   

   SELECT PROD_DESC FROM PRODUCTS_TBL

   UNION

   SELECT LAST_NAME FROM EMPLOYEE_TBL;

   
PROD_DESC
-----------------------
ASSORTED COSTUMES
ASSORTED MASKS
CANDY CORN
FALSE PARAFFIN TEETH
GLASS
KEY CHAIN
LIGHTED LANTERNS
OAK BOOKSHELF
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PLEW
PUMPKIN CANDY
SPURGEON
STEPHENS
WALLACE
WITCHES COSTUME

16 rows selected.

The PROD_DESC and LAST_NAME values are listed together, and the column heading taken is from the column name in the first query.

The UNION ALL Operator

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator. The UNION and UNION ALL operators are the same, although one returns duplicate rows of data where the other does not.

The syntax is as follows:

   
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
UNION ALL
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]

Look at the following example:

   
SELECT EMP_ID FROM EMPLOYEE_TBL
UNION ALL
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL

The preceding SQL statement returns all employee IDs from both tables and shows duplicates.

The following is the same compound query in the previous section with the UNION ALL operator:

   
   

   SELECT PROD_DESC FROM PRODUCTS_TBL

   UNION ALL

   SELECT PROD_DESC FROM PRODUCTS_TMP;

   
PROD_DESC
-----------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF

22 rows selected.

Notice that there were 22 rows returned in this query (9+6) because duplicate records are retrieved with the UNION ALL operator.

The INTERSECT Operator

The INTERSECT operator is used to combine two SELECT statements, but returns only rows from the first SELECT statement that are identical to a row in the second SELECT statement. Just as with the UNION operator, the same rules apply when using the INTERSECT operator.

The syntax is as follows:

   
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
INTERSECT
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]

Look at the following example:

   
SELECT CUST_ID FROM CUSTOMER_TBL
INTERSECT
SELECT CUST_ID FROM ORDERS_TBL;

The preceding SQL statement returns the customer identification for those customers who have placed an order.

The following example illustrates the INTERSECT using the two original queries in this hour:

   
   

   SELECT PROD_DESC FROM PRODUCTS_TBL

   INTERSECT

   SELECT PROD_DESC FROM PRODUCTS_TMP;

   
PROD_DESC
--------------------
ASSORTED COSTUMES
ASSORTED MASKS
CANDY CORN
FALSE PARAFFIN TEETH
KEY CHAIN
LIGHTED LANTERNS
OAK BOOKSHELF
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY
WITCHES COSTUME

11 rows selected.

Only eleven rows are returned because only eleven rows were identical between the output of the two single queries.

The EXCEPT Operator

The EXCEPT operator combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. Once again, the same rules that apply to the UNION operator also apply to the EXCEPT operator.

The syntax is as follows:

   
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
EXCEPT
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]

Study the following example:

   
   

   SELECT PROD_DESC FROM PRODUCTS_TBL

   EXCEPT

   SELECT PROD_DESC FROM PRODUCTS_TMP;

   
PROD_DESC
-----------------------
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY

3 rows selected.

According to the results, there were three rows of data returned by the first query that were not returned by the second query.

   
   

   SELECT PROD_DESC FROM PRODUCTS_TBL

   MINUS

   SELECT PROD_DESC FROM PRODUCTS_TMP;

   
PROD_DESC
-----------------------
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY

3 rows selected.