Queries that Join Tables

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > SELECT >

Queries that Join Tables

If the FROM clause specifies more than one table reference, the query can join rows from several tables. A join condition specifies a relationship between at least one column from each table to be joined. Because the columns in a join condition are being compared, they must have compatible data types.

The FROM clause of the SELECT statement can specify several types of joins.

FROM Clause Keyword	Corresponding Result Set
CROSS JOIN	Cartesian product (all possible pairs of rows)
INNER JOIN	Only rows from CROSS that satisfy the join condition
LEFT OUTER JOIN	Qualifying rows of one table, and all rows of another
RIGHT OUTER JOIN	Same as LEFT, but roles of the two tables are reversed
FULL OUTER JOIN	The union of all rows from an INNER join of the two tables, and of all rows of each table that have no match in the other table (using NULL values in the selected columns of the other table)

The last four categories are collectively called "Join Types" in the literature of the relational model; a CROSS JOIN ignores the specific data values in joined tables, returning every possible pair of rows, where one row is from each table.

In an inner (or simple) join, the result contains only the combination of rows that satisfy the join conditions. Outer joins preserve rows that otherwise would be discarded by inner joins. In an outer join, the result contains the combination of rows that satisfy the join conditions and the rows from the dominant table that would otherwise be discarded. The rows from the dominant table that do not have matching rows in the subordinate table contain NULL values in the columns selected from the subordinate table.

Dynamic Server supports the two different syntaxes for left outer joins:

Informix-extension syntax
ANSI-compliant syntax

Earlier versions of the database server supported only Informix-extension syntax for outer joins. Dynamic Server continues to support this legacy syntax, but using the ANSI-compliant syntax for joins provides greater flexibility in creating queries. In view definitions, however, the legacy syntax does not require materialized views, so it might offer performance advantages.

If you use ANSI-compliant syntax to specify a join in the FROM clause, you must also use ANSI-compliant syntax for all outer joins in the same query block. Thus, you cannot begin another outer join with only the OUTER keyword. The following query, for example, is not valid:

SELECT * FROM customer, OUTER orders RIGHT JOIN cust_calls
   ON (customer.customer_num = orders.customer_num)
   WHERE customer.customer_num = 104);

This returns an error because it attempts to combine the Informix-extension OUTER syntax with the ANSI-compliant RIGHT JOIN syntax for outer joins.

See the section Informix-Extension Outer Joins for the Informix-extension syntax for LEFT OUTER joins.