ANSI Joins

If the FROM clause specifies more than one table reference, the query can join rows from several tables or views. 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.

This syntax fragment is part of the FROM Clause.


ANSI Joins

     .-INNER----------------.                        (1)                    
|--+-+----------------------+--JOIN--| ANSI Tables |------| ON Clause |-+-->
   | '-+-LEFT--+--+-------+-'                                           |   
   |   +-RIGHT-+  '-OUTER-'                                             |   
   |   '-FULL--'                                                        |   
   |                             (1)                                    |   
   '-CROSS JOIN--| ANSI Tables |----------------------------------------'   

>--+-------------------+----------------------------------------|
   |               (2) |   
   '-+----+--alias-----'   
     '-AS-'                

ON Clause

|--ON----------------------------------------------------------->

   .-OR--------------------------------------------------------------------------------------.       
   |   .-AND-----------------------------------------------------------------------------.   |       
   V   V                                                   (3)                           |   | (7)   
>--------+-+------------+--column--| Relational Operator |------+------------+--column-+-+---+------|
         | +-alias .----+                                       +-alias .----+         |             
         | +-external .-+                                       +-external .-+         |             
         | +-table .----+                                       +-table .----+         |             
         | +-view .-----+                                       +-view .-----+         |             
         | '-synonym .--'                                       '-synonym .--'         |             
         |                         (4)                                                 |             
         +-| Function Expression |-----------------------------------------------------+             
         |               (5)                                                           |             
         +-| Condition |---------------------------------------------------------------+             
         +-(subquery)------------------------------------------------------------------+             
         |                           (6)                                               |             
         '-(-| Collection Subquery |-----)---------------------------------------------'

Notes:

Element	Description	Restrictions	Syntax
`alias`	Temporary alternative name declared in the FROM clause for a table or view	See Aliases for Tables or Views	Identifier
`column`	Column of a table or view to be joined	Must exist in the table or view	Identifier
`synonym, table, view`	Name of a synonym, table, or view to be joined in the query	Synonym and the table or view to which it points must exist. Must include appropriate qualifiers.	Database Object Name
`subquery`	Embedded query	Cannot contain the FIRST or the ORDER BY clause	SELECT statement

You must use the same form of join syntax (either Informix® extension or ANSI-compliant) for all of the outer joins in the same query block. When you use the ANSI-compliant join syntax, you must also specify the join condition in the ON clause.

Rows from the tables or views are joined when there is a match between the values of specified columns. When the columns to be joined have the same name, you must qualify each column name with its data source. If you join a table to itself, you must list the table name twice in the FROM clause and assign it two different table aliases. Then use the aliases to qualify the column names.

By default, the order in which the database server joins tables and views is independent of the order in which they are referenced in the FROM clause. To force the order in which table objects are joined to match the FROM clause order, you can specify the ORDERED optimizer directive after the SELECT keyword. For more information, see the section Join-Order Directive.

Join-method optimizer directives that you specify for an ANSI-compliant joined query are ignored, but are listed under Directives Not Followed in the explain output file.

Multiple join specifications require parentheses around the original table and join segment

The ANSI-Joined Table segment must be enclosed between parentheses if it is immediately followed by another join specification. For example, the first of the following two queries returns an error; the second query is valid:

SELECT * FROM (T1 LEFT JOIN T2) CROSS JOIN T3 ON (T1.c1 = T2.c5)
      WHERE (T1.c1 < 100);    -- Ambiguous order of operations;

SELECT * FROM (T1 LEFT JOIN T2 ON (T1.c1 = T2.c5)) CROSS JOIN T3 
      WHERE (T1.c1 < 100);    -- Unambiguous order of operations;

The following valid query specifies nested LEFT OUTER joins of table expressions within the FROM clause of the outer SELECT statement:

SELECT * FROM
( (SELECT C1,C2 FROM T3) AS VT3(V31,V32)
 LEFT OUTER JOIN
        ( (SELECT C1,C2 FROM T1) AS VT1(VC1,VC2)
        LEFT OUTER JOIN
        (SELECT C1,C2 FROM T2) AS VT2(VC3,VC4)
        ON VT1.VC1 = VT2.VC3)
ON VT3.V31 = VT2.VC3);

Join keyword definitions

In an ANSI-compliant join that specifies the LEFT, RIGHT, or FULL keywords in the FROM clause, the OUTER keyword is optional.

Table 1. Join keywords
FROM Clause Keywords	Corresponding Result Set
CROSS JOIN	The Cartesian product, which is all possible paired combinations that include one row from each of the joined tables.
INNER JOIN	All the rows in a table that have one or more matching rows in the other table (or tables). The unmatched rows are discarded.
LEFT OUTER JOIN	All the rows of the first table reference and the qualifying rows from the second table reference The LEFT keyword specifies a join that treats the first table reference as the dominant table in the join. In a left outer join, the subordinate part of the outer join appears to the right of the keyword that begins the outer join specification. The result set includes all the rows that an INNER join returns, plus all rows that would otherwise have been discarded from the dominant table.
RIGHT OUTER JOIN	The qualifying rows from the first table reference and all the rows of the second table reference The RIGHT keyword specifies a join that treats the second table reference as the dominant table in the join. In a right outer join, the subordinate part of the outer join appears to the left of the keyword that begins the outer join specification. The result set includes all the rows that an INNER join returns, plus all rows that would otherwise have been discarded from the dominant table.
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)