UnionSo far we've discussed finding the items that are common in two sets (intersection) and the items that are different (difference). The third type of set operation involves adding two sets (union).Union in Set TheoryUnion lets you combine two sets of similar information into one set. As a scientist, you might be interested in combining two sets of chemical or physical sample data. For example, a pharmaceutical research chemist might have two different sets of compounds that seem to provide a certain beneficial effect. The chemist can union the two sets to obtain a single list of all effective compounds. Let's take a look at union in action by examining two sets of numbers. The first set of numbers is as follows.1, 5, 8, 9, 32, 55, 78The second set of numbers is as follows.3, 7, 8, 22, 55, 71, 99The union of these two sets of numbers is the numbers in both sets combined into one new set.1, 5, 8, 9, 32, 55, 78, 3, 7, 22, 71, 99Note that the values common to both sets, 8 and 55, appear only once in the answer. Also, the sequence of the numbers in the result set is not necessarily in any specific order. When you ask a database system to perform a UNION, the values returned won't necessarily be in sequence unless you explicitly include an ORDER BY clause. In SQL, you can also ask for a UNION ALL if you want to see the duplicate members.The members of each set don't have to be just single values. In fact, you'll probably deal with sets of rows when working with SQL.To find the union of two or more sets of complex members, all the members in each set you're trying to union must have the same number and type of attributes. For example, suppose you have a complex set like the one below. Each row represents a member of the set (a stew recipe), and each column denotes a particular attribute (an ingredient).
A second set might look like the following.
The union of these two sets is the set of objects from both sets. Duplicates areeliminated.
A second query result set might look like this one. (These are recipes from Mike's cookbook).
The union of these two sets is all the rows in both sets. Maybe John and Mike decided to write a cookbook together, too!
Let's assume you have a nice database containing all your favorite recipes.You really like recipes with either beef or onions, so you want a list of recipes that contain either ingredient. Figure 7-5 (on page 232) shows you the set diagram that helps you visualize how to solve this problem.The upper circle represents the set of recipes that contain beef. The lower circle represents the set of recipes that contain onions. The union of the two circles gives you all the recipes that contain either ingredient, with duplicates eliminated where the two sets overlap. As you probably know, you first ask SQL to fetch all the recipes that have beef. In the second query, you ask SQL Figure 7-5 Finding out which recipes have either beef or onionsto fetch all the recipes that have onions. As you'll see later, the SQL keyword UNION links the two queries to get the final answer. By now you know that it's not a good idea to design a recipes database with a single table. Instead, a correctly designed recipes database will have a separate Recipe_Ingredients table with one row per recipe per ingredient. Each ingredient row will have only one ingredient, so no one row can be both beef or onions at the same time.You'll need to first find all the recipes that have a beef row, then find all the recipes that have an onions row, and then union them.Problems You Can Solve with UnionA union lets you "mush together"rows from two similar sets-with the added advantage of no duplicate rows. Here's a sample of the problems you can solve using a union technique with data from 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.""List the entertainers who played engagements for customer Bonnicksen combined with all the entertainers who played engagements for customer Rosales.""Show me the students who have an average score of 85 or better in Art together with the students who have an average score of 85 or better in Computer Science.""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.""Show me the recipes that have beef together with the recipes that have garlic."As with other "pure" set operations, one of the limitations is that the values must match in all the columns in each result set. This works well if you're unioning two or more sets from the same table-for example,customers who ordered bicycles and customers who ordered helmets. It also works well when you're performing a union on sets from tables that have like columns- for example, customer names and addresses and employee names and addresses. We'll explore the uses of the SQL UNION operator in detail in Chapter 10.In many cases where you would otherwise union rows from the same table, you'll find that using DISTINCT (to eliminate the duplicate rows) with complex criteria on joined tables will serve as well.We'll show you all about solving problems this way using JOINs in Chapter 8, INNER JOINs.