In a correctly designed relational database (third normal form or above), all non-key columns are about the key, the whole key, and nothing but the key, to use an excellent and frequently quoted formula.[*] Each row is both logically consistent and distinct from all other rows in the same table. It is this design characteristic that enables join relationships to be established within the same table. You can therefore select in the same query different (not necessarily disjoint) sets of rows from the same table and join them as if those rows came from several different tables. In this section, I'll discuss the simple self-join and exclude the more complex examples of nested hierarchies that I discuss later in Chapter 7.
Self-joins—tables joined to themselves—are much more common than
hierarchies. In some cases, it is simply because the data is seen in an
identical way, but from two different angles; for instance, we can
imagine that a query listing air flights would refer to the
airports table twice, once to find the name of
the departure airport, and once to find the name of the arrival airport.
select f.flight_number, a.airport_name departure_airport, b.airport_name arrival_airport from flights f, airports a, airports b where f.dep_iata_code = a.iata_code and f.arr_iata_code = b.iata_code
In such a case, the usual rules apply: what matters is to ensure that highly efficient index access takes place. But what if the criteria are such ...