SAND CDBMS SQL Reference Guide
Joins

INNER JOIN

The operation of simply joining two or more tables so that only the matching rows from each are returned — often called an equi-join — is here referred to as an inner join. This precision is necessary in order to distinguish inner joins syntactically from left, right, and full (outer) joins. For example, either of the inner join queries below (on the Part and Partsupp tables) will produce the same results:

   SELECT part.pno, pname, qty
     FROM part, partsupp
     WHERE part.pno = partsupp.pno;

      or

   SELECT part.pno, pname, qty
     FROM part INNER JOIN partsupp
     ON part.pno = partsupp.pno;

In both of these queries, the Part and Partsupp tables are joined on their pno columns. Notice that there is a syntactical structure in the second query that explicitly defines this query as an inner join. The key words INNER JOIN can be used in the FROM clause, instead of separating the joined tables with a comma. If the join syntax is used, an ON clause must be included to explicitly state the join condition.

Equi-joins or inner joins include a join condition requiring the values derived from one column of a table or view to equal the values derived from another column of a different table or view. Join conditions may be created on any combination of columns, provided the qualified column names being compared in the ON or WHERE clause have compatible data types (see the section Data Type Compatibility). Note that if the explicit join syntax is used, a WHERE clause can be used to further qualify the selection criteria of the ON clause.

For example, suppose the supplier numbers and city names for all suppliers that supply part "P2" are needed. The next query illustrates how this could be accomplished:

SELECT supplier.sno, city
FROM partsupp INNER JOIN supplier
ON partsupp.sno = supplier.sno
WHERE pno = "P2";

The query results appear below:

sno	city
S2	PARIS
S3	PARIS
S1	LONDON
S4	LONDON

When the tables to be joined have a common column name appearing in the WHERE clause join condition, these column names must be prefixed (qualified) with their table names to resolve any ambiguity. In the above query, the qualified column name partsupp.sno specifies that the sno column of the Partsupp table is to be compared with the sno column of the Supplier table (supplier.sno).

If a join condition (for example, partsupp.sno = supplier.sno) is not included in an equi-join query, the result is a Cartesian product. This type of join concatenates every record from a table with every record from the other tables listed in the FROM clause, including all this information in the query result. Cartesian products have limited practical value and can result in extremely large and often nonsensical result sets.

The result set of an inner join query cannot contain any unmatched rows from either table. For example, if the Part table contained a record for a "RIVET" with a pno column value of "P8", this "RIVET" information would not be retrieved in either of the previous queries, since there is no matching record in the Partsupp table with a pno column value of "P8".