Figure 7-6. Two records appear for Barbara Olson in the union because the rows are different. in Font

Generator PDF 417 in Font Figure 7-6. Two records appear for Barbara Olson in the union because the rows are different.

Figure 7-6. Two records appear for Barbara Olson in the union because the rows are different.
Generating PDF 417 In None
Using Barcode generator for Font Control to generate, create PDF417 image in Font applications.
www.OnBarcode.com
Barcode Encoder In None
Using Barcode drawer for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
We need to consider what we really want from such a union. If we need a list of names for a joint Christmas party for the two clubs, then we don t want anyone listed twice. The way to avoid duplicates is to project just the names from each table. The algebra expression is shown in Listing 7-5 and the SQL in Listing 7-6.
EAN 13 Creation In None
Using Barcode printer for Font Control to generate, create GTIN - 13 image in Font applications.
www.OnBarcode.com
Making ECC200 In None
Using Barcode maker for Font Control to generate, create ECC200 image in Font applications.
www.OnBarcode.com
CHAPTER 7 SE T OPERA TION S
Encode EAN / UCC - 13 In None
Using Barcode encoder for Font Control to generate, create USS-128 image in Font applications.
www.OnBarcode.com
Painting UPC Symbol In None
Using Barcode generation for Font Control to generate, create UPC Symbol image in Font applications.
www.OnBarcode.com
Listing 7-5. Relational Algebra to Project the Appropriate Columns Before the Union
Code 39 Extended Encoder In None
Using Barcode drawer for Font Control to generate, create Code 39 image in Font applications.
www.OnBarcode.com
GTIN - 8 Encoder In None
Using Barcode generator for Font Control to generate, create EAN8 image in Font applications.
www.OnBarcode.com
Listing 7-6. SQL to Project the Appropriate Columns Before the Union SELECT FamilyName, Name FROM ClubA UNION SELECT LastName, FirstName FROM ClubB
PDF-417 2d Barcode Encoder In .NET Framework
Using Barcode encoder for .NET framework Control to generate, create PDF417 image in .NET framework applications.
www.OnBarcode.com
Decode PDF 417 In Visual Basic .NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
With the query in Listing 7-6, we will now get just one row for Barbara in the union. This will, of course, depend on her name being spelled the same in both clubs. And what if there are actually two different people named Barbara Olson Sadly, real data is fraught with these sorts of problems, and there is little you can do other than be aware of them.
PDF-417 2d Barcode Printer In Java
Using Barcode generator for Java Control to generate, create PDF 417 image in Java applications.
www.OnBarcode.com
Creating EAN / UCC - 13 In .NET Framework
Using Barcode printer for ASP.NET Control to generate, create EAN / UCC - 13 image in ASP.NET applications.
www.OnBarcode.com
Uses for Union
Generating Barcode In Java
Using Barcode printer for Android Control to generate, create Barcode image in Android applications.
www.OnBarcode.com
Read Barcode In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
The main use for union is combining data from two or more tables, as we have been doing in the previous sections. For example, if data for different months had been stored in separate tables (not necessarily a great design decision!), you could use several union operations to combine the data for the whole year. It is also possible to combine two sets of rows from the one table. Say we wanted to find the IDs of all the people who have entered either tournament 24 or tournament 40 from the Entry table in Figure 7-7.
PDF 417 Decoder In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
QR Code JIS X 0510 Drawer In Java
Using Barcode generation for Android Control to generate, create QR-Code image in Android applications.
www.OnBarcode.com
Figure 7-7. Entry table
Create UPC-A Supplement 5 In Visual Basic .NET
Using Barcode generator for .NET framework Control to generate, create UPC-A image in .NET framework applications.
www.OnBarcode.com
Painting Barcode In Objective-C
Using Barcode printer for iPhone Control to generate, create Barcode image in iPhone applications.
www.OnBarcode.com
C HA PTER 7 S ET O PE RAT IO NS
QR Code Recognizer In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Barcode Decoder In .NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
We could get a list of all the IDs of members entering tournament 24 and a list of IDs of members entering tournament 40, and take the union. To get the list of IDs for tournament 24, we need to select the rows for that tournament (TourID = 24), and then project just the MemberID column. Similarly for tournament 40, we need to select the rows where the value of TourID is 40. The algebra and the SQL for the union are shown in Listings 7-7 and 7-8.
Listing 7-7. Relational Algebra for Finding IDs of Members Who Have Entered Either Tournament 24 or 40
Listing 7-8. SQL for Finding IDs of Members Who Have Entered Either Tournament 24 or 40 SELECT MemberID FROM Entry WHERE TourID = 24 UNION SELECT MemberID FROM Entry WHERE TourID = 40
While Listing 7-8 will find the correct IDs, most people would use the more straightforward query in Listing 7-9 to achieve the same result.
Listing 7-9. Finding IDs of Members Who Have Entered Either Tournament 24 or 40 SELECT MemberID FROM Tournament WHERE TourID = 24 OR TourID = 40
Another use for union is to perform the equivalent of a full outer join in products that don t support the FULL OUTER JOIN key phrase. Microsoft Access 2007 is one product that does not implement full outer joins explicitly. Let s recap the different types of outer joins that we discussed in 3. Figure 7-8 shows the different types of joins between the Member table (just a very little one!) and the Type table. All the joins are on MemberType = Type. The inner join would have just three rows. We would not get a row for William Cooper, as he does not have a value in MemberType, and we would not get a row for the Associate type, as no row in the Member has this value in the MemberType field. The left outer join ensures that we see all the rows from the left-hand table (Member); the right outer join gives us all rows from the right-hand table (Type); and the full outer join gives us all rows from both tables. Figure 7-8 illustrates these joins.
Copyright © OnBarcode.com . All rights reserved.