DATABASE

The SQL Programming Language : Complex Queries and Join Queries (part 2)

1/5/2011 9:37:21 AM

5 The Join with Projection

Projection refers to the idea that you might want to see only a limited number of columns in the results of a query. It is very useful for join queries, which can lead to a very large number of columns in the results.

Suppose you wanted to find the actorID, lastName, firstName, roleID, and roleName of all rows of the Actor and Role tables that matched on actorID. It is necessary to use qualified column names in two places in this query, in the joining condition and in the SELECT expression. The need for dot notation in the joining condition was explained in the previous example. The need for dot notation in the SELECT expression is similar. When joining the Actor and Role tables, you could specify either Actor.actorID or Role.actorID in the SELECT expression because the value retrieved by either would be the same; however, you have to use one or the other, and not just the actorID. Whenever a column name is used in a query and more than one table in the query has a column with that name, you must qualify the name using dot notation.

Example Query 5 illustrates a join with projection.

Example Query 5

SELECT Actor.actorID, lastName, firstName, roleID, roleName
FROM Actor, Role
WHERE Actor.actorID = Role.actorID;

The output of Example Query 5 would start as shown in the Example Query 5 Results.

Example Query 5 Results

actorIDlastNamefirstNameroleIDroleName
00001HanksTom00057Forrest Gump
00001HanksTom00063Jim Lovell
00002PaxtonBill00064Fred Haise
00003BaconKevin00054Captain Jack Ross
00003BaconKevin00065Jack Swigert
. . .. . .. . .. . .. . .

6 The Cartesian Product

The term "Cartesian product" refers to a situation where each row in one table is matched with each row in another table, regardless of the column values they contain. The Cartesian product is of theoretical importance, but it is rarely of practical use. If you are trying to write a join query and you forget to include the WHERE keyword and the joining condition, the result will be a Cartesian product. This is one of the easiest mistakes to make.

Consider Example Query 6. There are 100 actors in the Actor table and 138 roles in the Role table. Example Query 6 says to match each actor with each role, regardless of whether the actor played the role. There are 100 times 138 rows (13,800 rows) in the complete results. When you create a Cartesian product by accident, you will typically recognize your mistake by the number of rows in the result. The fact that the number of rows in the result is the arithmetic product of the numbers of rows in the two tables is a reminder of why this result is known as a product.

Example Query 6

SELECT Actor.actorID, lastName, firstName, roleID, roleName
FROM Actor, Role;

A representative subset of the output of the Cartesian product is shown in the Example Query 6 Results. Every actor is matched with the role Dorothy Gale. Buried in this list is Judy Garland, the actor who actually played that role.

Example Query 6 Results

actorIDlastNamefirstNameroleIDroleName
. . .. . .. . .. . .. . .
00059FurlongEdward00001Dorothy Gale
00046GableClark00001Dorothy Gale
00061GarlandJudy00001Dorothy Gale
00029GetzJohn00001Dorothy Gale
00027GoldblumJeff00001Dorothy Gale
. . .. . .. . .. . .. . .

7 Joins with Additional WHERE Conditions

The idea of complex queries involving multiple parts was introduced in the previous sections. Some of the most useful queries are those that join two tables and include WHERE conditions on one or more columns of either of the two tables involved. Suppose you wanted to find the actorID, lastName, firstName, roleID, and roleName for Jack Nicholson only. Such a query would join the Actor and Role tables. It would also have a condition on the Actor table, which would be included with the keyword AND. Example Query 7 illustrates this idea.

Example Query 7

SELECT Actor.actorID, lastName, firstName, roleID, roleName
FROM Actor, Role
WHERE Actor.actorID = Role.actorID
AND lastName = 'Nicholson' AND firstName = 'Jack';

The complete output of Example Query 7 is shown in the Example Query 7 Results.

Example Query 7 Results

actorIDlastNamefirstNameroleIDroleName
00019NicholsonJack00052Colonel Nathan R. Jessep
00019NicholsonJack00097The Joker
00019NicholsonJack00098Jack Napier

It is possible to create an interesting example of a join query that includes a condition testing for inequality between columns of the two joined tables. Suppose you wanted to find the actorID, lastName, actor's gender, roleID, role-Name, and role's gender for those cases where the gender of the actor and the gender of the role were not the same. Example Query 8 illustrates this idea. Note that dot notation must be used to distinguish the two columns named "gender."

Example Query 8

SELECT Actor.actorID, lastName, Actor.gender, roleID,
roleName, Role.gender
FROM Actor, Role
WHERE Actor.actorID = Role.actorID
AND Actor.gender <> Role.gender;

The complete output of Example Query 8 is shown in the Example Query 8 Results.

Example Query 8 Results

actorIDlastNameactor.genderroleIDroleNameRole.gender
00070HoffmanM00078Dorothy Michaels (Tootsie)F
00073WilliamsM00082Mrs. DoubtfireF

8 Joins with ORDER BY

Just as simple selection queries can be combined with ordering, grouping, and calculating, so, too, can join queries be combined with these features. For example, ORDER BY can be used with a join.

Suppose you wanted to find the movieID, lastName, firstName, roleID, and roleName for all actors and roles, ordered by movieID and lastName. Notice that movieID comes from the Role table, whereas lastName comes from the Actor table. The join is formed on the two tables Role and Actor, and the ordering of the results depends on the columns taken from each of these two tables. Example Query 9 illustrates this idea.

Example Query 9

SELECT movieID, lastName, firstName, roleID, roleName
FROM Actor, Role
WHERE Actor.actorID = Role.actorID
ORDER BY movieID, lastName;

The output of Example Query 9 would start as shown in the Example Query 5-9 Results.

Example Query 9 Results

movieIDlastNamefirstNameroleIDroleName
001BaconKevin00065Jack Swigert
001HanksTom00063Jim Lovell
001HarrisEd00067Gene Kranz
001PaxtonBill00064Fred Haise
001QuinlanKathleen00068Marilyn Lovell
001SiniseGary00066Ken Mattingly
002BergmanIngrid00017Ilsa Lund (Laszlo)
002BogartHumphrey00016Rick Blaine
002GreenstreetSydney00021Signor Ferrari
002HenreidPaul00018Victor Laszlo
002LorrePeter00022Ugarte
002RainsClaude00019Captain Renault
. . .. . .. . .. . .. . .

Other  
 
Video
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone