| 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 orderbut 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;  | The PRODUCTS_TMP table was created in Hour 3, "Managing Database Objects." Refer back to Hour 3 if you need to re-create this table. |
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. | | The EXCEPT operator is known as the MINUS operator in some implementations . Check your implementation for the operator name that performs the EXCEPT operator's function. |
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.  | 
