SQL Statements › SELECT › Select-Into Statement

Select-Into Statement

The select-into statement produces a result table consisting of at most, one row, and assigns the values in that row to host variables. If the table is empty, the statement assigns +100 to SQLCODE and does not assign values to the host variables.

Following is the syntax diagram for the select-into statement:

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

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

   ┌─ , ─────────────┐        ┌─ , ─────────┐
 ►─▼─ host-variable ─┴─ FROM ─▼─ table-ref ─┴─────────────────────────────────►

 ►─┬─────────────────────────┬────────────────────────────────────────────────►◄
   └─ WHEREsearch-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 table-ref shown in the syntax box immediately preceding the following one has syntax as follows:

►►─┬─┬─ table-name ─┬─┬────────────────────┬─┬────────────────────────────────►◄
   │ └─ view-name ──┘ └─ 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 for a LEFT JOIN 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.