Sams Teach Yourself SQL in 24 Hours
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- Hour 3. Managing Database Objects
- Hour 4. The Normalization Process
- Hour 5. Manipulating Data
- Hour 6. Managing Database Transactions
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- Hour 8. Using Operators to Categorize Data
- Hour 9. Summarizing Data Results from a Query
- Hour 10. Sorting and Grouping Data
- Hour 11. Restructuring the Appearance of Data
- Hour 12. Understanding Dates and Times
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Hour 14. Using Subqueries to Define Unknown Data
- Hour 15. Combining Multiple Queries into One
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- Hour 17. Improving Database Performance
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Hour 19. Managing Database Security
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- Hour 21. Working with the System Catalog
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- Hour 24. Extensions to Standard SQL
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- Appendix B. Using MySQL for Exercises
- Appendix C. Answers to Quizzes and Exercises
- Appendix D. CREATE TABLE Statements for Book Examples
- Appendix E. INSERT Statements for Data in Book Examples
- Appendix F. Glossary
- Appendix G. Bonus Exercises
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.
Now, combine the same two queries with the UNION operator, making a compound query.
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:
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:
Look at the following example:
|
|
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:
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:
Look at the following example:
|
|
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:
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:
Study the following example:
According to the results, there were three rows of data returned by the first query that were not returned by the second query.
Using an ORDER BY with a Compound Query | Next Section
Account Sign In
View your cart