Inner Queries Checking for Existence in Font

Encoding PDF 417 in Font Inner Queries Checking for Existence

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.

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)

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

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.

Listing 4-19. What Does This Query Return SELECT m.Lastname, m.FirstName FROM Member m WHERE EXISTS (SELECT * FROM Entry e)

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.

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.

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)

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.

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.