Hour 15. Combining Multiple Queries into One
What You’ll Learn in This Hour:
• An overview of the operators that combine queries
• When to use the commands to combine queries
• Using the GROUP BY clause with the compound operators
• Using the ORDER BY clause with the compound operators
• How to retrieve accurate data
In this hour, you learn how to combine SQL queries by using the UNION, UNION ALL, INTERSECT, and EXCEPT operators. Once again, you must check your particular implementation for any variations in the use of these operators.
Single Queries Versus Compound Queries
The single query is one SELECT statement, whereas the compound query includes two or more SELECT statements.
You form compound queries by using some type of operator to join the two queries. The UNION operator in the following examples joins two queries.
A single SQL statement could be written as follows:
SELECT EMP_ID, SALARY, PAY_RATE
FROM EMPLOYEE_PAY_TBL
WHERE SALARY IS NOT NULL OR
PAY_RATE IS NOT NULL;
This is the same statement using the UNION operator:
SELECT EMP_ID, SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY IS NOT NULL
UNION
SELECT EMP_ID, PAY_RATE
FROM EMPLOYEE_PAY_TBL
WHERE PAY_RATE IS NOT NULL;
The previous statements return pay information for all employees who are paid either hourly or on a salary.
Compound operators are used to combine and restrict the results of two SELECT statements. You can use these operators to return or suppress the output of duplicate records. Compound operators can bring together similar data that is stored in different fields.
By the Way: How UNION Works
If you executed the second query, the output has two column headings: EMP_ID and SALARY. Each individual’s pay rate is listed under the SALARY column. When using the UNION operator, column headings are determined by column names or column aliases used in the first SELECT statement.
Compound queries enable you to combine the results of more than one query to return a single set of data. Compound queries are often simpler to write than a single query with complex conditions. Compound queries also allow for more flexibility regarding the never-ending task of data retrieval.
Compound Query Operators
The compound query operators vary among database vendors. The American National Standards Institute (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 combines the results of two or more SELECT statements without returning 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. To use the UNION operator, each SELECT statement must have the same number of columns selected, the same number of column expressions, the same data type, and 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 statement from two tables:
SELECT PROD_DESC FROM PRODUCTS_TBL;
PROD_DESC
-----------------------
WITCH 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
--------------------
WITCH 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
WITCH COSTUME
KEY CHAIN
OAK BOOKSHELF
11 rows selected.
By the Way: Where the PRODUCTS_TMP Table Came From
The PRODUCTS_TMP table was created in Hour 3, “Managing Database Objects.” Refer to Hour 3 if you need to re-create this table.
In the first query, eleven rows of data were returned, and eleven rows of data were returned from the second query. Eleven rows of data are returned when the UNION operator combines the two queries. Only eleven rows are returned because duplicate rows of data are not returned when using the UNION operator.
The following code 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
WITCH COSTUME
16 rows selected.
The PROD_DESC and LAST_NAME values are listed together, and the column heading is taken from the column name in the first query.
The UNION ALL Operator
You use the UNION ALL operator 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 ]
The following SQL statement returns all employee IDs from both tables and shows duplicates:
SELECT EMP_ID FROM EMPLOYEE_TBL
UNION ALL
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
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
-----------------------
WITCH COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF
WITCH 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 (11+11) because duplicate records are retrieved with the UNION ALL operator.
The INTERSECT Operator
You use the INTERSECT operator to combine two SELECT statements, but it returns only rows from the first SELECT statement that are identical to a row in the second SELECT statement. The same rules apply when using the INTERSECT operator as when you used the UNION operator. Currently, the INTERSECT operator is not supported by MySQL 5.0 but is supported by both SQL Server and Oracle.
The syntax is as follows:
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
INTERSECT
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
The following SQL statement returns the customer identification for those customers who have placed an order:
SELECT CUST_ID FROM CUSTOMER_TBL
INTERSECT
SELECT CUST_ID FROM ORDERS_TBL;
The following example illustrates the INTERSECT operator 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
WITCH COSTUME
11 rows selected.
Only 11 rows are returned because only 11 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 EXCEPT operator is not currently supported in MySQL. In Oracle the EXCEPT operator is referenced by using the term MINUS but performs the same functionality.
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
EXCEPT
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
Study the following example, which would work in an SQL Server implementation:
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, three rows of data were returned by the first query that were not returned by the second query.
The following example demonstrates the use of the MINUS operator as a replacement for the EXCEPT operator:
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.
Using ORDER BY with a Compound Query
You can use the ORDER BY clause with a compound query. However, you can only use the ORDER BY clause to order the results of both queries. Therefore, there can be only one ORDER BY clause in a compound query, even though the compound query might consist of multiple individual queries or SELECT statements. The ORDER BY clause must reference the columns being ordered by an alias or by the column number.
The syntax is as follows:
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
OPERATOR{UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ ORDER BY ]
The following SQL statement returns the employee ID from EMPLOYEE_TBL and EMPLOYEE_PAY_TBL, but it does not show duplicates and it orders by EMP_ID:
SELECT EMP_ID FROM EMPLOYEE_TBL
UNION
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
ORDER BY 1;
By the Way: Using Numbers in the ORDER BY Clause
The column in the ORDER BY clause is referenced by the number 1 instead of the actual column name.
The results of the compound query are sorted by the first column of each query. Sorting compound queries lets you easily recognize duplicate records.
The following example shows the use of the ORDER BY clause with a compound query. You can use the column name in the ORDER BY clause if the column sorted by has the same name in all individual queries of the statement.
SELECT PROD_DESC FROM PRODUCTS_TBL
UNION
SELECT PROD_DESC FROM PRODUCTS_TBL
ORDER BY PROD_DESC;
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
WITCH COSTUME
11 rows selected.
The following query uses a numeric value in place of the actual column name in the ORDER BY clause:
SELECT PROD_DESC FROM PRODUCTS_TBL
UNION
SELECT PROD_DESC FROM PRODUCTS_TBL;
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
WITCH COSTUME
11 rows selected.
Using GROUP BY with a Compound Query
Unlike ORDER BY, you can use GROUP BY in each SELECT statement of a compound query, but you also can use it following all individual queries. In addition, you can use the HAVING clause (sometimes used with the GROUP BY clause) in each SELECT statement of a compound statement.
The syntax is as follows:
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
OPERATOR {UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
[ ORDER BY ]
In the following example, you select a literal string to represent customer records, employee records, and product records. Each query is simply a count of all records in each appropriate table. The GROUP BY clause groups the results of the entire report by the numeric value 1, which represents the first column in each query.
SELECT 'CUSTOMERS' TYPE, COUNT(*)
FROM CUSTOMER_TBL
UNION
SELECT 'EMPLOYEES' TYPE, COUNT(*)
FROM EMPLOYEE_TBL
UNION
SELECT 'PRODUCTS' TYPE, COUNT(*)
FROM PRODUCTS_TBL
GROUP BY 1;
TYPE COUNT(*)
----------- --------
CUSTOMERS 15
EMPLOYEES 6
PRODUCTS 9
3 rows selected.
The following query is identical to the previous query, except that the ORDER BY clause is used as well:
SELECT 'CUSTOMERS' TYPE, COUNT(*)
FROM CUSTOMER_TBL
UNION
SELECT 'EMPLOYEES' TYPE, COUNT(*)
FROM EMPLOYEE_TBL
UNION
SELECT 'PRODUCTS' TYPE, COUNT(*)
FROM PRODUCTS_TBL
GROUP BY 1
ORDER BY 2;
TYPE COUNT(*)
----------- --------
EMPLOYEES 6
PRODUCTS 9
CUSTOMERS 15
3 rows selected.
This is sorted by column 2, which was the count on each table. Hence, the final output is sorted by the count from least to greatest.
Watch Out!: Bad Data Results
Incomplete data returned by a query qualifies as incorrect data.
Retrieving Accurate Data
Be cautious when using the compound operators. Incorrect or incomplete data might be returned if you use the INTERSECT operator and you use the wrong SELECT statement as the first individual query. In addition, consider whether you want duplicate records when using the UNION and UNION ALL operators. What about EXCEPT? Do you need any of the rows that the second query did not return? As you can see, the wrong compound query operator or the wrong order of individual queries in a compound query can easily cause misleading data to be returned.
Summary
This hour introduced you to compound queries. All SQL statements previous to this hour have consisted of a single query. Compound queries allow multiple individual queries to be used together as a single query to achieve the data resultset desired as output. The compound query operators discussed included UNION, UNION ALL, INTERSECT, and EXCEPT (MINUS). UNION returns the output of two single queries without displaying duplicate rows of data. UNION ALL simply displays all output of single queries, regardless of existing duplicate rows. INTERSECT returns identical rows between two queries. EXCEPT (the same as MINUS) returns the results of one query that do not exist in another query. Compound queries provide greater flexibility when trying to satisfy the requirements of various queries, which, without the use of compound operators, could result in complex queries.
Q&A
Q. How are the columns referenced in the GROUP BY clause in a compound query?
A. The columns can be referenced by the actual column name or by the number of the column placement in the query if the column names are not identical in the two queries.
Q. I understand what the EXCEPT operator does, but would the outcome change if I were to reverse the SELECT statements?
A. Yes. The order of the individual queries is important when using the EXCEPT or MINUS operator. Remember that all rows are returned from the first query that are not returned by the second query. Changing the order of the two individual queries in the compound query could definitely affect the results.
Q. Must the data type and the length of columns in a compound query be the same in both queries?
A. No. Only the data type must be the same. The length can differ.
Q. What determines the column names when using the UNION operator?
A. The first query set determines the column names for the data returned when using a UNION operator.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
Refer to the syntax covered in this hour for the following quiz questions when referring to the INTERSECT and EXCEPT operators. Remember that MySQL does not currently support these two operators.
1. Is the syntax correct for the following compound queries? If not, what would correct the syntax? Use EMPLOYEE_TBL and EMPLOYEE_PAY_TBL as follows:
EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)
EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LAST_RAISE DATE,
SALARY DECIMAL(8,2),
BONUS DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL (EMP_ID)
a. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
UNION
SELECT EMP_ID, POSITION, DATE_HIRE
FROM EMPLOYEE_PAY_TBL;
b. SELECT EMP_ID FROM EMPLOYEE_TBL
UNION ALL
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID;
c. SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
INTERSECT
SELECT EMP_ID FROM EMPLOYEE_TBL
ORDER BY 1;
2. Match the correct operator to the following statements.
Exercises
Refer to the syntax covered in this hour for the following exercises. You might have to write your queries by hand because MySQL does not support some of the operators covered in this hour. When you are finished, compare your results to ours.
Use CUSTOMER_TBL and ORDERS_TBL as listed:
CUSTOMER_TBL
CUST_IN VARCHAR(10) NOT NULL primary key,
CUST_NAME VARCHAR(30) NOT NULL,
CUST_ADDRESS VARCHAR(20) NOT NULL,
CUST_CITY VARCHAR(15) NOT NULL,
CUST_STATE VARCHAR(2) NOT NULL,
CUST_ZIP INTEGER(5) NOT NULL,
CUST_PHONE INTEGER(10),
CUST_FAX INTEGER(10)
ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key,
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY INTEGER(6) NOT NULL,
ORD_DATE DATETIME
1. Write a compound query to find the customers who have placed an order.
2. Write a compound query to find the customers who have not placed an order.