“I beseech those whose piety will permit them reverently to petition, that they will pray for this union.”
—SAM HOUSTON, TEXAS HERO
In Chapter 7, “Thinking in Sets,” I introduced three fundamental set operations—intersection, difference, and union. Chapter 8, “INNER JOINs,” showed how to perform the equivalent of an intersection operation by linking result sets on key values using INNER JOIN. Chapter 9, “OUTER JOINs,” discussed how to ask for a set difference by using an OUTER JOIN and testing for the Null value. This chapter explains how to do the third operation, a UNION.
A UNION lets you select the rows from two or more similar result sets and combine them into a single result set. Notice that I said “rows,” not “columns.” In Chapters 8 and 9, you learned how to bring together columns from two or more result sets using a JOIN. When you ask for a JOIN, the columns from the result sets appear side by side. For example, if you ask for the RecipeClassDescription from the Recipe_Classes table and the RecipeTitle from the Recipes table with a JOIN, you get a result set that looks like Figure 10-1.
Let’s first take a quick look at the syntax for a basic UNION, as shown in Figure 10-2.
A UNION interleaves the rows from one result set with the rows from another result set. You define each result set by writing a SELECT statement that can include not only a complex JOIN in the FROM clause but also WHERE, HAVING, and GROUP BY clauses. You then link them with the UNION keyword. (You’ll learn about the GROUP BY clause in Chapter 13, “Grouping Data,” and the HAVING clause in Chapter 14, “Filtering Grouped Data.”) If you ask for RecipeClassDescription from the Recipe_Classes table UNION RecipeTitle from the Recipes table, you get an answer that looks like Figure 10-3.
Notice that I get only one column in the result set. The name of the column is inherited from the column in the first table we chose to include in the SELECT expression, but it includes information on both RecipeTitle (Asparagus) and RecipeClassDescription (Dessert). Instead of appearing side by side, the data from the two columns is interleaved vertically.
If you studied the diagram in Figure 10-2, you’re probably wondering what the optional keyword ALL is about. When you leave out that keyword, your database system eliminates any rows that have duplicate values. For example, if there’s a RecipeClassDescription of Dessert and a RecipeTitle of Dessert, you get only one Dessert row in the final result set. Conversely, when you include the ALL keyword, no duplicate rows are removed. Note that UNION ALL is likely to be much more efficient because your database system doesn’t have to do extra work to look for and eliminate any duplicate rows. If you’re certain that the queries you are combining with UNION don’t contain any duplicate rows (or you don’t care about duplicates), then always use the ALL keyword.
To perform a UNION, the two result sets must meet certain requirements. First, each of the two SELECT statements that you’re linking with a UNION must have the same number of output columns specified after the SELECT keyword so that the result set will have the same number of columns. Secondly, each corresponding column must be what the SQL Standard calls “comparable.”
Note: The full SQL:2016 Standard allows you to UNION dissimilar sets. However, most commercial
implementations support the basic or entry-level standard I’m describing here. You
might find that your database system allows you to use UNION in more creative ways.
As discussed in Chapter 6, “Filtering Your Data,” you should compare only character values with character values, number values with number values, or datetime values with datetime values. Although some database systems allow mixing data types in a comparison, it really doesn’t make sense to compare a character value such as “John” to a numeric value such as 55. If it makes sense to compare two columns in a WHERE clause, then the columns are comparable. This is what the SQL Standard means when it requires that a column from one result set that you want to UNION with a column from another result set must be of a comparable data type.
In the previous chapters on INNER JOIN and OUTER JOIN, you studied how to construct a SELECT statement using the SELECT, FROM, and WHERE clauses. The focus of those two chapters was on constructing complex JOINs within the FROM clause. To construct a UNION, you now have to graduate to a SELECT expression that links two or more SELECT statements with the UNION operator. Each SELECT statement can have as simple or complex a FROM clause as you need to get the job done.
Let’s start simply by creating a UNION of two simple SELECT statements that use a single table in the FROM clause. Figure 10-4 shows the syntax diagram for a UNION of two simple SELECT statements.
Unlike when you ask for a JOIN, all the action happens in the UNION operator that you specify to combine the two SELECT statements. As mentioned earlier, if you leave out the optional ALL keyword, your database system eliminates any duplicate rows it finds. This means that the result set from your request might have fewer rows than the sum of the number of rows returned from each result set participating in the UNION. On the other hand, if you include the ALL keyword, the number of rows in the result set will be equal to the sum of the number of rows in the two participating result sets.
Note: The SQL Standard also defines a CORRESPONDING clause that you can place after the
UNION keyword to indicate that you want the UNION performed by comparing columns that
have the same name in each result set. You can also further restrict the comparison
set by including a specific list of column names after the CORRESPONDING keyword.
I could not find a major commercial implementation of this feature, but you might
find it supported in future releases of the product you use.
Let’s create a simple UNION—a mailing list for customers and vendors from the Sales Orders sample database. Figure 10-5 shows the two tables needed.
Notice that there’s no “natural” relationship between these two tables, but they do both contain columns that have similar meanings and data types. In a mailing list, you need a name, street address, city, state, and ZIP Code. Because all these fields in both tables are comparable character data, I don’t need to worry about data types. (Some database designers might make ZIP Code a number, but that’s OK too, as long as the ZIP Code column from one table is a data type that’s comparable with the data type of the ZIP Code column from the second table.)
One problem is that the name in the Vendors table is a single column, but there are two name fields in Customers: CustFirstName and CustLastName. To come up with the same number of columns from both tables, I need to build an expression on the two columns from Customers to create a single column expression to UNION with the single name column from Vendors. Let’s build the query.
Note: Throughout this chapter, I use the “Request/Translation/Clean Up/SQL” technique introduced
in Chapter 4, “Creating a Simple Query.”
“Build a single mailing list that consists of the name, address, city, state, and ZIP Code for customers and the name, address, city, state, and ZIP Code for vendors.”
|
Translation |
Select customer full name, customer address, customer city, customer state, and customer ZIP Code from the customers table combined with vendor name, vendor address, vendor city, vendor state, and vendor ZIP Code from the vendors table |
|
Clean Up |
Select customer full name, customer address, customer city, customer state, and customer ZIP Code from the customers table combined with union Select vendor name, vendor address, vendor city, vendor state, and vendor ZIP Code from the vendors table |
|
|
|
Notice that each SELECT statement generates five columns, but I had to use an expression to combine the two name columns in the Customers table into a single column. All the columns from both SELECT statements are character data, so I have no problem with them being comparable.
You might be wondering: “What are the names of the columns that are output from this query?” Good question! The SQL Standard specifies that when the names of respective columns are the same (for example, the name of the fourth column of the first SELECT statement is the same as the name of the fourth column of the second SELECT statement), that’s the name of the output column. If the column names are different (as in the example I just constructed), the SQL Standard states: “If a <query expression body> immediately contains UNION or INTERSECT, and the <column name>s of a pair of corresponding columns of the operand tables are not equivalent, then the result column has an implementation-dependent <column name>.”
In plain English, this means that your database system decides what names to assign to the output columns. Your system is compliant with the SQL Standard as long as the name doesn’t appear in some other column position in one of the result sets participating in the UNION. Most commercial database systems default to the names of the columns in the first SELECT statement. For the previous example, this means that you’ll see column names of MailingName, CustStreetAddress, CustCity, CustState, and CustZipCode.
Notice that I did not include the ALL keyword in the UNION. Although it is unlikely that a customer last name and first name will match a vendor name (never mind the address, city, state, and ZIP Code), I wanted to avoid duplicate mailing addresses. If you’re certain that you won’t have any duplicates in two or more UNION sets, you can include the ALL keyword. Using ALL most likely will cause the request to run faster because your database system won’t have to do extra work attempting to remove duplicates.
As you might imagine, the SELECT statements you combine with a UNION operator can be as complex as you need to get the job done. The only restriction is that both SELECT statements must ultimately provide the same number of columns, and the columns in each relative position must be comparable data types.
Suppose you want a list of all the customers and the bikes they ordered combined with all the vendors and the bikes they supply. First, let’s identify all the tables I need. Figure 10-6 shows the tables needed to link customers to products.
Looks like I need to JOIN four tables. If I want to find vendors and the products they sell, I need the tables shown in Figure 10-7.
As discussed in Chapter 8, you can nest multiple JOIN clauses to link several tables to gather the information you need to solve a complex problem. For review, Figure 10-8 shows the syntax for nesting three tables.
I now have all the pieces needed to solve the puzzle. I can build a compound INNER JOIN to fetch the customer information, insert a UNION keyword, and then build the compound INNER JOIN for the vendor information.
“List customers and the bikes they ordered combined with vendors and the bikes they sell.”
|
Translation |
Select customer full name and product name from the customers table joined with the orders table on customer ID in the customers table matches customer ID in the orders table, then joined with the order details table on order number in the orders table matches order number in the order details table, and then joined with the products table on product number in the products table matches product number in the order details table where product name contains ‘bike’, combined with select vendor name and product name from the vendors table joined with the product vendors table on vendor ID in the vendors table matches vendor ID in the product vendors table, and then joined with the products table on product number in the products table matches product number in the product vendors table where product name contains ‘bike’ |
|
Select customer full name and product name from the customers table joined with the orders table on customers.customer ID in the customers table matches = orders.customer ID in the orders table, then joined with the order details table on orders.order number in the orders table matches = order_details.order number in the order details table, and then joined with the products table on products.product number in the products table matches = order_details.product number in the order details table where product name contains like ‘%bike%’, combined with union select vendor name and product name from the vendors table joined with the product vendors table on vendors.vendor ID in the vendors table matches = product_vendors.vendor ID in the product vendors table, and then joined with the products table on products.product number in the products table matches = product_vendors.product number in the product vendors table where product name contains like ‘%bike%’ |
|
|
|
|
Well, that’s about the size of the King Ranch, but it gets the job done! Notice that I also threw in a character string literal that I named RowID in both SELECT statements so that it will be easy to see which rows originate from Customers and which ones come from Vendors. You might be tempted to insert a DISTINCT keyword in the first SELECT statement because a really good customer might have ordered a particular bike model more than once. Because I didn’t use the ALL keyword on the UNION, the request will eliminate any duplicates anyway. If you add DISTINCT, you might be asking your database system to perform extra work to eliminate duplicates twice!
When you need to build a UNION query, I recommend that you build the separate SELECT statements first. It’s easy then to copy and paste the syntax for each SELECT statement into a new query, separating each statement with the UNION keyword.
So far, I have shown you only how to use a UNION to combine two result sets. In truth, you can follow the second SELECT statement specification with another UNION keyword and another SELECT statement. Although some implementations limit the number of result sets you can combine with UNION, in theory, you can keep adding UNION SELECT to your heart’s content.
Suppose you need to build a single mailing list from three different tables—Customers, Employees, and Vendors—perhaps to create a combined list for holiday greeting labels. Figure 10-9 shows a diagram of the syntax to build this list.
You can see that you need to create one SELECT statement to fetch all the names and addresses from the Customers table, UNION that first statement with a SELECT statement for the same information from the Employees table, and finally, UNION that with a SELECT statement for names and addresses from the Vendors table. (To simplify the process, I have combined the Translation and Clean Up steps in this example.)
“Create a single mailing list for customers, employees, and vendors.”
|
Translation/Clean Up |
Select customer full name, customer street address, customer city, customer state, and customer ZIP Code from the customers table combined with union Select employee full name, employee street address, employee city, employee state, and employee ZIP Code from the employees table combined with union Select vendor name, vendor street address, vendor city, vendor state, and vendor ZIP Code from the vendors table |
|
|
|
Of course, if you want to filter the mailing list for a particular city, state, or range of ZIP Codes, you can add a WHERE clause to any or all of the SELECT statements. If, for example, you want to create a list of the customers, employees, and vendors only in a particular state, you must add a WHERE clause to each of the embedded SELECT statements. You could also apply a filter to just one of the SELECT statements, for example, to create a list of vendors in the state of Texas combined with all customers and all employees.
What about sorting the result of a UNION? You’ll find on many database systems that the result set appears as though it is sorted by the output columns from left to right. For example, in the UNION of three tables I just built in the previous section, the rows will appear in sequence first by name, then by street address, and so on.
To keep the postal service happy (and perhaps get a discount for a large mailing), sort your rows by ZIP Code. You can add an ORDER BY clause to do this, but the trick is that this clause must appear at the very end after the last SELECT statement. The ORDER BY applies to the result of the UNION, not the last SELECT statement. Figure 10-10 shows how to do this.
As the diagram shows, you can loop through a UNION SELECT statement as many times as you like to pick up all the result sets you need to combine, but the ORDER BY clause must appear at the end. You might ask, “What do I use for column_name or column_# in the ORDER BY clause?” Remember that you’re sorting the output of all the previous parts of the SELECT expression. As discussed earlier, the output names of the columns are “implementation-dependent,” but most database systems use the column names generated by the first SELECT statement.
You can also specify the relative column number, starting with 1, as the first output column. In a query that outputs name, street address, city, state, and ZIP Code, you need to specify a column_# of 5 (ZIP Code is the fifth column) to sort by ZIP.
Let’s sort the mailing list query using both techniques. Here’s the correct syntax for sorting by column name:
|
|
|
Of course, I’m assuming that the name of the output column I want to sort is the name of the column from the first SELECT statement. Using a relative column number to specify the sort looks like this:
|
|
|
You probably won’t use UNION as much as INNER JOIN and OUTER JOIN. You most likely will use UNION to combine two or more similar result sets from different tables. Although you can use UNION to combine two result sets from the same table or set of tables, you usually can solve those sorts of problems with a simple SELECT statement containing a more complex WHERE clause. I include a couple of examples in the “Sample Statements” section and show you the more efficient way to solve the same problem with a WHERE clause instead of a UNION.
Here’s just a small sample of the types of problems you can solve with UNION using the sample databases:
“Show me all the customer and employee names and addresses.”
“List all the customers who ordered a bicycle combined with all the customers who ordered a helmet.” (This is one of those problems that can also be solved with a single SELECT statement and a complex WHERE clause.)
“Produce a mailing list for customers and vendors.”
“List the customers who ordered a bicycle together with the vendors who provide bicycles.”
“Create a list that combines agents and entertainers.”
“Display a combined list of customers and entertainers.”
“Produce a list of customers who like contemporary music together with a list of entertainers who play contemporary music.”
“Create a mailing list for students and staff.”
“Show me the students who have an average score of 85 or better in Art together with the faculty members who teach Art and have a proficiency rating of 9 or better.”
“Find the bowlers who had a raw score of 155 or better at Thunderbird Lanes combined with bowlers who had a raw score of 140 or better at Bolero Lanes.” (This is another problem that can also be solved with a single SELECT statement and a complex WHERE clause.)
“List the tourney matches, team names, and team captains for the teams starting on the odd lane together with the tourney matches, team names, and team captains for the teams starting on the even lane.”
“Create an index list of all the recipe titles and ingredients.”
“Display a list of all ingredients and their default measurement amounts together with ingredients used in recipes and the measurement amount for each recipe.”
You now know the mechanics of constructing queries using UNION and have seen some of the types of requests you can answer with a UNION. Let’s take a look at a fairly robust set of samples using UNION from each of the sample databases. These examples illustrate the use of the UNION operation to combine sets of rows.
I’ve also included sample result sets that would be returned by these operations and placed them immediately after the SQL syntax line. The name that appears immediately above a result set is the name I gave each query in the sample data on the companion website for this book, www.informit.com/title/9780134858333. I stored each query in the appropriate sample database (as indicated within the example), and I prefixed the names of the queries relevant to this chapter with “CH10.” You can follow the instructions in the Introduction of this book to load the samples onto your computer and try them.
Note: Because many of these examples use complex JOINs, the optimizer for your database
system might choose a different way to solve these queries. For this reason, the first
few rows might not exactly match the result you obtain, but the total number of rows
should be the same. To simplify the process, I have combined the Translation and Clean
Up steps for all the following examples.
“Show me all the customer and employee names and addresses, including any duplicates, sorted by ZIP Code.”
|
Translation/Clean Up |
Select customer first name, customer last name, customer street address, customer city, customer state, and customer ZIP Code from the customers table combined with union all Select employee first name, employee last name, employee street address, employee city, employee state, and employee ZIP Code from the employees table, order by ZIP Code |
|
|
CH10_Customers_UNION_ALL_Employees (36 rows)
|
CustFirst Name |
CustLast Name |
CustStreet Address |
CustCity |
CustState |
CustZip Code |
|
Estella |
Pundt |
2500 Rosales Lane |
Dallas |
TX |
75260 |
|
Robert |
Brown |
672 Lamont Ave |
Houston |
TX |
77201 |
|
Kirk |
DeGrasse |
455 West Palm Ave |
San Antonio |
TX |
78284 |
|
Kirk |
DeGrasse |
455 West Palm Ave |
San Antonio |
TX |
78284 |
|
Angel |
Kennedy |
667 Red River Road |
Austin |
TX |
78710 |
|
Maria |
Patterson |
3445 Cheyenne Road |
El Paso |
TX |
79915 |
|
Mark |
Rosales |
323 Advocate Lane |
El Paso |
TX |
79915 |
|
Caleb |
Viescas |
4501 Wetland Road |
Long Beach |
CA |
90809 |
|
<< more rows here >> |
|||||
(Notice that Kirk DeGrasse must be both a customer and an employee.)
“List all the customers who ordered a bicycle combined with all the customers who ordered a helmet.”
|
Select customer first name, customer last name, and the constant ‘Bike’ from the customers table joined with the orders table on customers.customer ID in the customers table matches = orders.customer ID in the orders table, then joined with the order details table on orders.order number in the orders table matches = order_details.order number in the order details table, and then joined with the products table on product number in the products table matches = order_details.product number in the order details table where product name contains like ‘%bike%,’ combined with union Select customer first name, customer last name, and the constant ‘Helmet’ from the customers table joined with the orders table on customers.customer ID in the customers table matches = orders.customer ID in the orders table, then joined with the order details table on orders.order number in the orders table matches = order_details.order number in the order details table, and then joined with the products table on product number in the products table matches = order_details.product number in the order details table where product name contains like ‘%helmet%’ |
|
|
|
|
CH10_Customer_Order_Bikes_UNION_Customer_Order_Helmets (52 rows)
|
CustFirstName |
CustLastName |
ProdType |
|
Alaina |
Hallmark |
Bike |
|
Andrew |
Cencini |
Bike |
|
Andrew |
Cencini |
Helmet |
|
Angel |
Kennedy |
Bike |
|
Angel |
Kennedy |
Helmet |
|
Caleb |
Viescas |
Bike |
|
Caleb |
Viescas |
Helmet |
|
Darren |
Gehring |
Bike |
|
<< more rows here >> |
||
Notice that this is one of those problems that can also be solved with a single SELECT statement and a slightly more complex WHERE clause. The one advantage of using a UNION is that it’s easy to add an artificial “set identifier” column (in this case, the ProdType column) to each result set so that you can see which customers came from which result set. However, most database systems solve a WHERE clause—even one with complex criteria—much faster than they solve a UNION. Following is the SQL to solve the same problem with a WHERE clause, but note that this eliminates rows in which a customer ordered both a bike and a helmet because I didn’t include the ProdType column. Doing so would have required a CASE statement in the SELECT clause, which you won’t learn about until Chapter 19, “Condition Testing.”
|
|
|
CH10_Customers_Bikes_Or_Helmets (27 rows)
|
CustFirstName |
CustLastName |
|
Alaina |
Hallmark |
|
Andrew |
Cencini |
|
Angel |
Kennedy |
|
Caleb |
Viescas |
|
Darren |
Gehring |
|
David |
Smith |
|
Dean |
McCrae |
|
Estella |
Pundt |
|
<< more rows here >> |
|
Note: You can see that you need a DISTINCT keyword to eliminate duplicates when you don’t
use UNION. Remember that UNION automatically eliminates duplicates unless you specify
UNION ALL. You can specify DISTINCT in the UNION examples, but you’re asking your
database system to do more work than necessary.
“Create a list that combines agents and entertainers.”
|
Translation/Clean Up |
Select agent full name, and the constant ‘Agent’ from the agents table combined with union Select entertainer stage name, and the constant ‘Entertainer’ from the entertainers table |
|
|
|
CH10_Agents_UNION_Entertainers (22 rows)
|
Name |
Type |
|
Bishop, Scott |
Agent |
|
Carol Peacock Trio |
Entertainer |
|
Caroline Coie Cuartet |
Entertainer |
|
Coldwater Cattle Company |
Entertainer |
|
Country Feeling |
Entertainer |
|
Dumbwit, Daffy |
Agent |
|
Jazz Persuasion |
Entertainer |
|
Jim Glynn |
Entertainer |
|
<< more rows here >> |
|
“Show me the students who have a grade of 85 or better in Art together with the faculty members who teach Art and have a proficiency rating of 9 or better.”
|
Translation/Clean Up |
Select student first name aliased as FirstName, student last name aliased as LastName, and grade aliased as Score from the students table joined with the student schedules table on students.student ID in the students table matches = student_schedules.student ID in the student schedules table, then joined with the student class status table on student_class_status.class status in the student class status table matches = student_schedules.class status in the student schedules table, then joined with the classes table on classes.class ID in the classes table matches = student_schedules.class ID in the student schedules table, and then joined with the subjects table on subjects.subject ID in the subjects table matches = classes.subject ID in the classes table where class status description is = ‘completed’ and grade is greater than or equal to >= 85 and category ID is = ‘ART’ combined with union Select staff first name, staff last name, and proficiency rating aliased as Score from the staff table joined with the faculty subjects table on staff.staff ID in the staff table matches = faculty_subjects.staff ID in the faculty subjects table, and then joined with the subjects table on subjects.subject ID in the subjects table matches = faculty_subjects.subject ID in the faculty subjects table where proficiency rating is greater than > 8 and category ID is = ‘ART’ |
|
|
CH10_Good_Art_Students_And_Faculty (12 rows)
|
FirstName |
LastName |
Score |
Type |
|
Alaina |
Hallmark |
10 |
Faculty |
|
George |
Chavez |
97.81 |
Student |
|
John |
Kennedy |
87.65 |
Student |
|
Kerry |
Patterson |
99.83 |
Student |
|
Liz |
Keyser |
10 |
Faculty |
|
Mariya |
Sergienko |
9 |
Faculty |
|
Michael |
Hernandez |
10 |
Faculty |
|
<< more rows here >> |
|||
“List the tourney matches, team names, and team captains for the teams starting on the odd lane together with the tourney matches, team names, and team captains for the teams starting on the even lane, and sort by tournament date and match number.”
|
Translation/Clean Up |
Select tourney location, tourney date, match ID, team name, captain name and the constant ‘Odd Lane’ from the tournaments table joined with the tourney matches table on tournaments.tourney ID in the tournaments table equals = tourney_matches.tourney ID in the tourney matches table, then joined with the teams table on tourney_matches.odd lane team ID in the tourney matches table equals = teams.team ID in the teams table, and then joined with the bowlers table on teams.captain ID in the teams table equals = bowlers.bowler ID in the bowlers table, combined with union all Select tourney location, tourney date, match ID, team name, captain name and the constant ‘Even Lane’ from the tournaments table joined with the tourney matches table on tournaments.tourney ID in the tournaments tab‑le equals = tourney_matches.tourney ID in the tourney matches table, then joined with the teams table on tourney_matches.even lane team ID in the tourney matches table equals = teams.team ID in the teams table, and then joined with the bowlers table on teams.captain ID in the teams table equals = bowlers.bowler ID in the bowlers table, order by tourney date 2, and match ID 3 |
|
|
Notice that the two SELECT statements are almost identical! The only difference is the first SELECT statement links Tourney_Matches with Teams on OddLaneTeamID, and the second uses EvenLaneTeamID. Also, note that I decided in the final solution to sort by relative column number (the second and third columns) rather than column name (TourneyDate and MatchID). Finally, you can use UNION ALL because a team is never going to compete against itself.
CH10_Bowling_Schedule (114 rows)
|
Tourney Location |
Tourney Date |
MatchID |
TeamName |
Captain |
Lane |
|
Red Rooster Lanes |
2017-09-04 |
1 |
Marlins |
Fournier, David |
Odd Lane |
|
Red Rooster Lanes |
2017-09-04 |
1 |
Sharks |
Patterson, Ann |
Even Lane |
|
Red Rooster Lanes |
2017-09-04 |
2 |
Barracudas |
Sheskey, Richard |
Even Lane |
|
Red Rooster Lanes |
2017-09-04 |
2 |
Terrapins |
Viescas, Carol |
Odd Lane |
|
Red Rooster Lanes |
2017-09-04 |
3 |
Dolphins |
Viescas, Suzanne |
Odd Lane |
|
Red Rooster Lanes |
2017-09-04 |
3 |
Orcas |
Thompson, Sarah |
Even Lane |
|
Red Rooster Lanes |
2017-09-04 |
4 |
Manatees |
Viescas, Michael |
Odd Lane |
|
Red Rooster Lanes |
2017-09-04 |
4 |
Swordfish |
Rosales, Joe |
Even Lane |
|
Thunderbird Lanes |
2017-09-11 |
5 |
Marlins |
Fournier, David |
Even Lane |
|
Thunderbird Lanes |
2017-09-11 |
5 |
Terrapins |
Viescas, Carol |
Odd Lane |
|
<< more rows here >> |
|||||
“Create an index list of all the recipe classes, recipe titles, and ingredients.”
|
Translation/Clean Up |
Select recipe class description, and the constant ‘Recipe Class’ from the recipe classes table combined with union Select recipe title, and the constant ‘Recipe’ from the recipes table combined with union Select ingredient name, and the constant ‘Ingredient’ from the ingredients table |
|
|
CH10_Classes_Recipes_Ingredients (101 rows)
|
IndexName |
Type |
|
Asparagus |
Ingredient |
|
Asparagus |
Recipe |
|
Bacon |
Ingredient |
|
Balsamic vinaigrette dressing |
Ingredient |
|
Beef |
Ingredient |
|
Beef drippings |
Ingredient |
|
Bird’s custard powder |
Ingredient |
|
Black olives |
Ingredient |
|
<< more rows here >> |
|
I began the chapter by defining UNION and showing you the difference between linking two tables with a JOIN and combining two tables with a UNION.
I next explained how to construct a simple UNION using two SELECT statements, each of which asked for columns from a single table. I explained the significance of the ALL keyword and recommended that you use it either when you know the queries produce no duplicates or when you don’t care. I then progressed to combining two complex SELECT statements that each used a JOIN on multiple tables. Next, I showed how to use UNION to combine more than two result sets. I wrapped up my discussion of UNION syntax by showing how to sort the result.
I explained how UNION is useful and listed a variety of requests that you can solve using UNION. The “Sample Statements” section showed you one or two examples of how to use UNION in each of the sample databases, including the logic behind constructing these requests.
The following section presents some requests that you can work out on your own.
Below, I show you the request statement and the name of the solution query in the sample databases. If you want some practice, you can work out the SQL you need for each request and then check your answer with the query I saved in the samples. Don’t worry if your syntax doesn’t exactly match the syntax of the queries I saved—as long as your result set is the same.
1. “List the customers who ordered
a helmet together with the vendors who provide helmets.”
(Hint: This involves creating a UNION of two complex JOINs.)
You can find the solution in CH10_Customer_Helmets_Vendor_Helmets (91 rows).
1. “Display a combined list of customers and entertainers.”
(Hint: Be careful to create an expression for one of the names so that you have the same number of columns in both SELECT statements.)
You can find the solution in CH10_Customers_UNION_Entertainers (28 rows).
2. “Produce a list of customers who like contemporary music together with a list of entertainers who play contemporary music.”
(Hint: You need to UNION two complex JOINs to solve this one.)
You can find the solution in CH10_Customers_Entertainers_Contemporary (5 rows).
1. “Create a mailing list for students and staff, sorted by ZIP Code.”
(Hint: Try using a relative column number for the sort.)
You can find the solution in CH10_Student_Staff_Mailing_List (45 rows).
1. “Find the bowlers who had a raw score of 165 or better at Thunderbird Lanes combined with bowlers who had a raw score of 150 or better at Bolero Lanes.”
(Hint: This is another of those problems that can also be solved with a single SELECT statement and a complex WHERE clause.)
You can find the solution using UNION in CH10_Good_Bowlers_TBird_Bolero_UNION (129 rows). You can find the solution using WHERE in CH10_Good_Bowlers_TBird_Bolero_WHERE (135 rows).
2. “Can you explain why the row counts are different in the previous solution queries?”
(Hint: Try using UNION ALL in the first query.)
1. “Display a list of all ingredients and their default measurement amounts together with ingredients used in recipes and the measurement amount for each recipe.”
(Hint: You need one simple JOIN and one complex JOIN to solve this.)
You can find the solution in CH10_Ingredient_Recipe_Measurements (144 rows).
