We have seen how to choose, and order, columns and rows from a single table. But suppose the information we need from a single query is spread over many tables. SQL provides two main methods to access such information. The first of these uses relational joins, and is discussed in this section. The second involves subqueries, and is treated in a later section. The following discussion assumes a basic familiarity with the concept of joins from the relational algebra section.

A cross join (Cartesian product) of tables pairs all the rows of one with all the rows of the other. In SQL-89, a cross join of tables is specified by listing the tables in the from clause, using a comma to denote the Cartesian product operator (depicted by " " in relational algebra). A conditional join ( Q-join) selects only those rows from the Cartesian product that satisfy a specified condition. In SQL-89, the condition is specified in a where clause, just as we did in relational algebra. So the query expressions in Table 11.10 are equivalent.

Table 11.10: SQL-89 syntax for joins.

Although the SQL-89 syntax has the advantage of being supported by every commercial dialect of SQL, it has drawbacks. First, it fails to distinguish join conditions (intertable comparisons) from nonjoin conditions (intratable comparisons on the same base row), instead lumping them together in a single where clause. This makes the query harder to understand. Second,...