SQL Statements › SELECT › Subselect

Subselect

The subselect is a component of:

1. The full-select statement.
2. The CREATE VIEW statement.
3. The INSERT statement.
4. Certain predicates, which, in turn, are components of a subselect. A subselect that is a component of another subselect is called a subquery.

Following is the syntax diagram for the subselect statement:

►►─ SELECT ─┬────────────────┬────────────────────────────────────────────────►
            └─┬─ ALL ◄ ────┬─┘
              └─ DISTINCT ─┘

 ►─┬─ * ────────────────────────────────────────────┬─ FROM ──────────────────►
   │ ┌─ , ────────────────────────────────────────┐ │
   └─▼─┬─ expression ─┬───────────────────────┬─┬─┴─┘
       │              └─ AS ─ sql-identifier ─┘ │
       └─┬─ table-name.* ───────┬───────────────┘
         ├─ view-name.* ────────┤
         └─ correlation-name.* ─┘

   ┌─ , ─────────┐
 ►─▼─ table-ref ─┴─┬─────────────────────────┬────────────────────────────────►
                   └─ WHEREsearch-condition ─┘

 ►─┬──────────────────────────────┬─┬──────────────────────────┬──────────────►◄
   │            ┌─ , ───────────┐ │ │                          │
   └─ GROUP BY ─▼─ column-name ─┴─┘ └─ HAVINGsearch-condition ─┘

The table-ref shown in the syntax box immediately preceding the following one has syntax as follows:

►►─┬─┬─ table-name ─┬─┬──────┬─┬────────────────────┬─┬───────────────────────►◄
   │ └─ view-name ──┘ └─ AS ─┘ └─ correlation-name ─┘ │
   └─ alternate-join-type ────────────────────────────┘

The alternate-join-type shown in the syntax box immediately preceding the following one has syntax as follows:

►►─┬─────┬─ table-ref ─┬─ INNER ────────────┬─────────────────────────────────►
   └─ ( ─┘             └─ LEFT ─┬─────────┬─┘
                                └─ OUTER ─┘

 ►─ JOIN ─ table-ref ─┬───────────────┬─┬─────┬───────────────────────────────►◄
                      └─ ON ─ s-cond ─┘ └─ ) ─┘

Note: The s-cond (search-condition) specified in the optional ON clause differs from the one in the WHERE clause in that the ON clause defines the join conditions that determine which rows contain nulls, as opposed to the WHERE clause, which eliminates rows from the result entirely. Also note that if you use the optional parentheses, they must be balanced. That is, if you use an open parenthesis, you must also use a close parenthesis.

The previously shown JOIN syntax is compatible with Ingres, DB2, and ANSI SQL3 Core SQL.