• 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.
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.
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.
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 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.
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.
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.
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 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.
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;
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.
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.
Incomplete data returned by a query qualifies as incorrect 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.
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. 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.
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.
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.
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.
3.144.252.204