ANSI-Compliant Joins (IDS)

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

ANSI-Compliant Joins (IDS)

The ANSI-compliant syntax for joins supports these join specifications:

To use a CROSS join, a LEFT OUTER, RIGHT OUTER, or FULL OUTER join, or an INNER (or simple) join, see Creating an ANSI Join and ANSI INNER Joins.
To use pre-join filters, see Using the ON Clause.
To use one or more post-join filters in the WHERE clause, see Specifying a Post-Join Filter.
To have the dominant or subordinate part of an outer join be the result set of another join, see Using a Join as the Dominant or Subordinate Part of an Outer Join.

Important:

Use the ANSI-compliant syntax for joins when you create new queries in Dynamic Server. In Dynamic Server releases earlier than IDS 10.00.xC3, cross-server distributed queries with ANSI-compliant joins use query plans that are inefficient for this release. For any UDR older than IDS 10.00.xC3 that performs a cross-server ANSI-compliant join, use the UPDATE STATISTICS statement to replace the original query plan with a reoptimized plan.

ANSI Table Reference

This diagram shows the ANSI-compliant syntax for a table reference.

ANSI Table Reference:
 
|--+---+-synonym-------------+----+--------------------+----------------------+--|
   |   +-table---------------+    '-+---------+--alias-'                      |
   |   +-view----------------+      |    (1)  |                               |
   |   '-ONLY--+-(table )--+-'      '-AS------'                               |
   |           '-(synonym)-'                                                  |
   |  (2)   (1)   (3)                                (4)                      |
   '------------------+-| Collection Derived Table |------------------------+-'
                      |              (5)                                    |
                      +-| Iterator |----------------------------------------+
                      |                        (6)                          |
                      '-| ANSI Joined Tables |-------+--------------------+-'
                                                     '-+---------+--alias-'
                                                       |    (1)  |
                                                       '-AS------'

Notes:

Informix extension
Stored Procedure Language only
ESQL/C only
See Collection-Derived Table
See Iterator Functions (IDS)
See ANSI Joined Tables

Element	Description	Restrictions	Syntax
alias	Temporary name for a table or view within the scope of the SELECT	See The AS Keyword.	Identifier
synonym, table, view	Source from which to retrieve data	The synonym and the table or view to which it points must exist	Database Object Name

Here the ONLY keyword is the same semantically as in the Informix-extension Table Reference segment, as described in The ONLY Keyword (IDS).

The AS keyword is optional when you declare an alias (also called a correlation name) for a table reference, as described in The AS Keyword, unless the alias conflicts with an SQL keyword.

Creating an ANSI Join

With ANSI-compliant joined table syntax, as shown in the following diagram, you can specify the INNER JOIN, CROSS JOIN, NATURAL JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (and FULL OUTER JOIN keywords. The OUTER keyword is optional in ANSI-compliant outer joins.

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 syntax, you must also specify the join condition in the ON clause, as described in Using the ON Clause.

ANSI Joined Tables

This is the ANSI-compliant syntax for specifying inner and outer joins.

ANSI Joined Tables:
 
                                    .-----------------------------------------------------.
                              (1)   V                                                     |
|--+-| ANSI Table Reference |---------+-| Join Options |--------------------------------+-+-+--|
   |                                  |                                       (1)  (2)  |   |
   |                                  '-CROSS--JOIN--| ANSI Table Reference |-----------'   |
   '-(--| ANSI Joined Tables |--)-----------------------------------------------------------'
 
Join Options:
 
   .-INNER-----------------------.                                 (1)
|--+-+-LEFT---------+--+-------+-+--JOIN--| ANSI Table Reference |-------| ON Clause |--|
     |  (3)         |  '-OUTER-'
     +--------RIGHT-+
     |  (3)         |
     '--------FULL--'
 
ON Clause:
 
         .-AND---------------------------------------.
         V            (4)                            |
|--ON--+---+-| Join |------------------------------+-+---+------|
       |   |                         (5)           |     |
       |   +-| Function Expression |---------------+     |
       |   |               (2)                     |     |
       |   +-| Condition |-------------------------+     |
       |   +-(subquery)----------------------------+     |
       |   |                           (6)  (3)    |     |
       |   '-(-| Collection Subquery |-----------)-'     |
       | .-OR------------------------------------------. |
       | V            (4)                              | |
       '---+-| Join |--------------------------------+-+-'
           |                         (5)             |
           +-| Function Expression |-----------------+
           |               (2)                       |
           +-| Condition |---------------------------+
           +-(subquery)------------------------------+
           |    (3)                           (6)    |
           '-(--------| Collection Subquery |------)-'

Notes:

See ANSI Table Reference
See Condition
Dynamic Server only
See Specifying a Join in the WHERE Clause
See Function Expressions
See Collection Subquery

Element	Description	Restrictions	Syntax
subquery	Embedded query	Cannot contain the FIRST or the ORDER BY clause	SELECT

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

ANSI CROSS Joins

The CROSS keyword specifies the Cartesian product, returning all possible paired combinations that include one row from each of the joined tables.

ANSI INNER Joins

To create an inner (or simple) join using the ANSI-compliant syntax, specify the join with the JOIN or INNER JOIN keywords. If you specify only the JOIN keyword, the database server creates an implicit inner join by default. An inner join returns all the rows in a table that have one or more matching rows in the other table (or tables). The unmatched rows are discarded.

ANSI LEFT OUTER Joins

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.

ANSI RIGHT OUTER Joins

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.

ANSI FULL OUTER Joins

The FULL keyword specifies a join in which the result set includes all the rows from the Cartesian product for which the join condition is true, plus all the rows from each table that do not match the join condition.

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

Optimizer directives that you specify for an ANSI-compliant joined query are ignored, but are listed under Directives Not Followed in sqlexplain.out.