Hack 13. Choose the Right Join Style for Your Relationships

When a relationship between tables is optional, you need an OUTER JOIN. When querying over many changes, if you require an OUTER JOIN you sometimes have to change all the other INNER JOINs into OUTER JOINs.

There are two common patterns of JOINs: the chain and the star, as shown in Figure 2-1 and Figure 2-2. Each is described in the following sections.

Figure 2-1. A JOIN chain

Figure 2-2. A JOIN star


2.7.1. A JOIN Chain

There are two references in this JOIN chain example. The reference from trip, shown in Table 2-7, to budget, shown in Table 2-8, is optionalusers may put a NULL value in the budget field of a trip row. The link from budget to staff (shown in Table 2-9) is mandatory; every row of the budget table must have a value in the budgetHolder field. Therefore, you use an OUTER JOIN when querying across trip and budget and an INNER JOIN when querying across budget and staff.

Table 2-7. The trip table

tripID description budget
TR01 Sicily NULL
TR02 Egypt CTH22

Table 2-8. The budget table

budgetId description budgetHolder(NOT NULL)
CT22 Officer's mess ST02

Table 2-9. The staff table

staffId name rank
ST01 Yossarian Captain
ST02 Milo Lieutenant

If you want to list all of the trips with associated budget details, you must use a LEFT OUTER JOIN. Not all trips are associated with a budget; the LEFT OUTER JOIN will include those with and without a budget:

mysql> SELECT tripID, trip.description,budget.description
 -> FROM trip LEFT OUTER JOIN budget ON (trip.budget=budget.budgetID);
| tripID | description | description |
| TR01 | Sicily | NULL |
| TR02 | Egypt | Officer's Mess |

You could also specify the FROM clause as FROM budget RIGHT OUTER JOIN TRip ON trip.budget=budget.budgetId.

If you want to include the name of the budget holder you need to JOIN with the staff table. As NULL is not permitted in the budgetHolder column, you might think that an INNER JOIN (the default) would give the correct results, but that is not so:

mysql> SELECT tripID, trip.description,budget.description, name
 -> FROM trip LEFT OUTER JOIN budget ON (trip.budget=budget.budgetID)
 -> INNER JOIN staff ON (budgetHolder=staffID);
| tripID | description | description | name |
| TR02 | Egypt | Officer's Mess | Milo |

The chain of JOINs is calculated from left to right, so the result of the LEFT JOIN in the previous query would be INNER JOINed to the budget table. Because TR02 has a NULL value for the budget, the join condition filters that row out. You could bracket or reorder the JOINs so that the INNER JOIN is calculated first, but you give the optimizer more room to maneuver if you just continue the LEFT OUTER JOIN:

mysql> SELECT tripID, trip.description,budget.description, name
 -> FROM trip LEFT OUTER JOIN budget ON (trip.budget=budget.budgetID)
 -> LEFT OUTER JOIN staff ON (budgetHolder=staffID);
| tripID | description | description | name |
| TR01 | Sicily | NULL | NULL |
| TR02 | Egypt | Officer's Mess | Milo |


2.7.2. A JOIN Star

The star pattern comprises one central table; the satellite tables are related to rows in the central table. These relationships may be optional or mandatory.

In this example, the central table is flightTicket. All flight tickets originate from an airport but only some of them were sold by an agent and only some of them involve passengers with a frequent flyer account (freqFlyer):

CREATE TABLE flightTicket
,agent CHAR(4) NULL 
,departFrom CHAR(3) NOT NULL 
,freqFlyer CHAR(4) NULL 
,FOREIGN KEY (agent) REFERENCES agent(id)
,FOREIGN KEY (departFrom) REFERENCES airport(id)
,FOREIGN KEY (freqFlyer) REFERENCES freqFlyer(id)

In a star schema, use the LEFT OUTER JOIN only for the tables that need it. The order of the joins does not matter:

mysql> SELECT airport.name AS airport,
 ->  agent.name AS agent,
 ->  freqFlyer.name freqFlyer
 -> FROM flightTicket LEFT OUTER JOIN agent ON (agent =agent.id)
 -> INNER JOIN airport ON (departFrom=airport.id)
 -> LEFT OUTER JOIN freqFlyer ON (freqFlyer =freqFlyer.id);
| airport | agent | freqFlyer |
| Edinburgh | NULL | NULL |
| Edinburgh | Smokehouse | NULL |
| Heathrow | Smokehouse | Bill Peters |
| Heathrow | NULL | John Weak |

Because the tables are linked to each other only through the central table, the central table's requirements dictate the JOINs. A NULL value for agent does not affect how freqFlyer or airport relates to flightTicket, and neither does a NULL value for freqFlyer affect how airport or agent relates to flightTicket.

SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance


Users and Administration

Wider Access


SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net