Inner Queries Checking for Existence
PDF417 Creator In None
Using Barcode creator for Font Control to generate, create PDF417 image in Font applications.www.OnBarcode.com
ANSI/AIM Code 128 Creation In None
Using Barcode creation for Font Control to generate, create Code-128 image in Font applications.www.OnBarcode.com
Another type of inner query is the one we saw working with the EXISTS keyword. A statement using EXISTS just looks to see whether any rows at all are returned by the inner query. The actual values or number of rows returned are not important. Listing 4-18 returns any rows from the Member table where we can find a matching row (with the same value for MemberID) in the Entry table.
Paint QR-Code In None
Using Barcode generation for Font Control to generate, create Denso QR Bar Code image in Font applications.www.OnBarcode.com
Paint UCC.EAN - 128 In None
Using Barcode printer for Font Control to generate, create GS1-128 image in Font applications.www.OnBarcode.com
Listing 4-18. SQL Statement to Retrieve Members Who Have Entered a Tournament SELECT m.Lastname, m.FirstName FROM Member m WHERE EXISTS (SELECT * FROM Entry e WHERE e.MemberID = m.MemberID)
ECC200 Generator In None
Using Barcode drawer for Font Control to generate, create Data Matrix image in Font applications.www.OnBarcode.com
GS1 - 12 Generation In None
Using Barcode printer for Font Control to generate, create UPC Symbol image in Font applications.www.OnBarcode.com
Because the actual values retrieved by the inner query are not important, the inner query usually has the form SELECT * FROM. Another feature of this type of query is that the inner and outer sections are usually correlated. By this we mean that the WHERE clause in the inner section refers to values in the table in the outer section. This allows us to compare values in two tables at once, and
Code-39 Creation In None
Using Barcode maker for Font Control to generate, create Code 3/9 image in Font applications.www.OnBarcode.com
Painting ISBN - 13 In None
Using Barcode printer for Font Control to generate, create ISBN image in Font applications.www.OnBarcode.com
CHAPTER 4 NES TE D QUERIES
PDF-417 2d Barcode Encoder In None
Using Barcode encoder for Office Excel Control to generate, create PDF-417 2d barcode image in Office Excel applications.www.OnBarcode.com
PDF 417 Creator In Java
Using Barcode creator for Java Control to generate, create PDF-417 2d barcode image in Java applications.www.OnBarcode.com
I find the easiest way to visualize this is as illustrated in Figure 4-4. We write out a member s name (from the outer section) if there is a matching row in the Entry table (inner section). It is difficult to think of a sensible EXISTS query that doesn t correlate values in the inner and outer sections. Consider Listing 4-19.
Code128 Decoder In Visual C#.NET
Using Barcode scanner for Visual Studio .NET Control to read, scan read, scan image in Visual Studio .NET applications.www.OnBarcode.com
Printing Code39 In C#.NET
Using Barcode maker for .NET framework Control to generate, create Code 3/9 image in .NET applications.www.OnBarcode.com
Listing 4-19. What Does This Query Return SELECT m.Lastname, m.FirstName FROM Member m WHERE EXISTS (SELECT * FROM Entry e)
Denso QR Bar Code Creation In Java
Using Barcode generator for BIRT Control to generate, create QR Code image in BIRT applications.www.OnBarcode.com
USS Code 39 Generation In None
Using Barcode encoder for Software Control to generate, create Code 39 Full ASCII image in Software applications.www.OnBarcode.com
Listing 4-19 doesn t really make any sense. It says to write out each member s name if there is a row in the Entry table (any row!). If the Entry table is empty, we will get nothing returned; otherwise, we will get all the names of all the members. I can t think why you d ever want to do that. EXISTS queries are useful when we are looking for matching values somewhere else, and that is why the select condition needs to compare values from both the inner and outer sections.
Painting ANSI/AIM Code 128 In Visual Studio .NET
Using Barcode drawer for Reporting Service Control to generate, create Code 128A image in Reporting Service applications.www.OnBarcode.com
Reading Barcode In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.www.OnBarcode.com
Using Nested Queries for Updating
Barcode Encoder In Objective-C
Using Barcode creator for iPhone Control to generate, create Barcode image in iPhone applications.www.OnBarcode.com
Paint PDF 417 In None
Using Barcode generation for Software Control to generate, create PDF 417 image in Software applications.www.OnBarcode.com
This book is mainly about queries for retrieving data, but many of the same ideas can be used for updating data and adding or deleting records. In 1 we looked at simple queries such as updating the phone number of a particular member, as in Listing 4-20.
UPC-A Supplement 5 Maker In Objective-C
Using Barcode maker for iPhone Control to generate, create UPC-A Supplement 2 image in iPhone applications.www.OnBarcode.com
Barcode Printer In VS .NET
Using Barcode generator for Reporting Service Control to generate, create Barcode image in Reporting Service applications.www.OnBarcode.com
Listing 4-20. Updating a Single Phone Number UPDATE Member m SET m.Phone = '875076' WHERE m.MemberID = 118
We can also update several records at a time; for example, we could update some aspect of all the senior members by changing the WHERE clause in Listing 4-20. In 1 we also looked at inserting and deleting rows from a table. Listing 4-21 shows a simple example of inserting a row into the Entry table. We list the columns we are providing values for and then the values.
Listing 4-21. Inserting a Row into the Entry Table INSERT INTO Entry (MemberID, TourID, Year) VALUES (153, 25, 2007)
CHAPTER 4 NES TED QUER IES
Now let s consider the situation where we want to add an entry for tournament 25 in 2007 for each of the juniors in the club. We want to add a set of rows to the Entry table, as shown in Figure 4-6, where the left column has the member IDs for all the juniors and the next two columns are the specific tournament (25) and year (2007) for each entry.
Figure 4-6. Rows to be added to Entry table
We can write an SQL query to return a set of rows like those in Figure 4-6, as shown in Listing 4-22. This query is a little different from others we have looked at because it has constants in the SELECT clause. It will construct a row for each junior member with the member s ID and the two constants 25 (for the tournament) and 2007 (for the year).
Listing 4-22. Constructing New Entry Rows for Junior Members SELECT MemberID, 25, 2007 FROM Member WHERE MemberType = 'Junior'
Now we can use Listing 4-22 as a subquery in our insert query in Listing 4-21. Rather than provide just one value with the VALUES keyword, we can provide a set of values resulting from the subquery. Listing 4-23 shows how we can do this. The inner SELECT query will produce the set of rows in Figure 4-6, and the outer INSERT query will put them in the table.
Listing 4-23. Inserting Entries for Juniors into Tournament 25 for 2007 INSERT INTO Entry (MemberID, TourID, Year) SELECT MemberID, 25, 2007 FROM Member WHERE MemberType = 'Junior'
The same potential for using nested queries applies to other updating issues. Say, for the purpose of finding an example, that after entering data in the Entry table for the 2007 social tournament at Kaiapoi (tournament 25), you realize that only players with handicaps of 20 or more were allowed to enter. You could use a nested query to delete entries for members with handicaps less than 20, as shown in Listing 4-24.