SQL初心者なのでtableをJOINするときの文法で、別名をつける記法があることを知らない、しかもASは省略できるってよ

疑問

SELECT
    `foo`.`id` AS t0_r0,
    `foo`.`xxx` AS t0_r1,
    `assets`.`id` AS t1_r0,
    `assets`.`name` AS t1_r1,
    `assets`.`type` AS t1_r2,
    `assets_bar`.`id` AS t3_r0,
    `assets_bar`.`name` AS t3_r1,
    `assets_bar`.`type` AS t3_r2,
    `foo_assets`.`id` AS t4_r0,
    `foo_assets`.`xxx` AS t4_r1,
    `foo_assets`.`asset_id` AS t4_r2,
    `foo_assets`.`bar_id` AS t4_r3,
FROM
    `foo`
    LEFT OUTER JOIN
        `assets`
    ON  `assets`.`id` = `foo`.`asset_id`
    LEFT OUTER JOIN
        `bar`
    ON  `bar`.`id` = `foo`.`bar_id`
    LEFT OUTER JOIN
        `number_indices`
    ON  `number_indices`.`number_hash` = `bar`.`number_hash`
    AND `number_indices`.`type` IN('AIndex', 'BIndex')
    LEFT OUTER JOIN
        `assets` `assets_bar`
    ON  `assets_bar`.`id` = `number_indices`.`asset_id`
    LEFT OUTER JOIN
        `foo` `foo_assets`
    ON  `foo_assets`.`asset_id` = `assets_bar`.`id`

若干省略しましたが上のようなSQLをみたとき、assets_barもfoo_assetsというtable名らしきものが見えます。

しかしDBを眺めてもそんな名前のtableは見当たりません。なのにクエリを実行すると実行できてしまいます。

一体どこから来たものなのでしょうか？

手がかり

    LEFT OUTER JOIN
        `assets`
    ON  `assets`.`id` = `foo`.`asset_id`

    LEFT OUTER JOIN
        `assets` `assets_bar`
    ON  `assets_bar`.`id` = `number_indices`.`asset_id`

この2つをよく見比べるとどっちにもassetsがみえます。それに

`assets` `assets_bar`

という間のスペース、なにか気になります。

それにSELECTしているカラム名を見ると

SELECT
    `foo`.`id` AS t0_r0,
    `foo`.`xxx` AS t0_r1,
    `assets`.`id` AS t1_r0,
    `assets`.`name` AS t1_r1,
    `assets`.`type` AS t1_r2,
    `assets_bar`.`id` AS t3_r0,
    `assets_bar`.`name` AS t3_r1,
    `assets_bar`.`type` AS t3_r2,
    `foo_assets`.`id` AS t4_r0,
    `foo_assets`.`xxx` AS t4_r1,
    `foo_assets`.`asset_id` AS t4_r2,
    `foo_assets`.`bar_id` AS t4_r3,

assets assets_barの2つってやっぱり似てますよね・・・？

謎解き

こんなとき見るべきは規格書です。

まずSQLのjoinの文法を見ます。ISOから規格書買ってくるのは面倒なので、適当なところから拾ってます、ご容赦を。

7.12.1 Specification format and rules for joined tables : Hitachi Advanced Database SQL Reference

joined-table ::= {cross-join|qualified-join|(joined-table)}
 
 
  cross-join ::= table-reference CROSS JOIN table-primary
 
  qualified-join ::= table-reference [{INNER|{LEFT|RIGHT|FULL} [OUTER]}] JOIN [join-method-specification] table-reference join-specification
    join-specification ::= ON search-condition

構文と上のSQLを見比べると

`assets` `assets_bar`

はtable-referenceに該当しそうです。

table-reference:

Specifies a table or a joined table to be joined. For details about table references, see 7.11 Table reference.

7.11章を見に行くように言われたので見に行きます。

7.11.1 Specification format for table references : Hitachi Advanced Database SQL Reference

table-reference ::= {table-primary | joined-table}
 
  table-primary ::= {table-name [[AS] correlation-name][index-specification]
                 | query-name [[AS] correlation-name]
                 | derived-table [[AS] correlation-name [(derived-column-list)]]
                 | table-function-derived-table [AS] correlation-name (table-function-column-list)
                 | (joined-table)}

table-referenceはtable-primaryもしくはjoined-tableのことと定義されています。

table-primaryの定義を見るとその中にtable-name [[AS] correlation-name][index-specification]というのがあります。これが求めていたものでしょうか？

table-name:

Specifies the table from which to retrieve data. For rules on specifying a table name, see (2) Table name specification format.

• To retrieve data from a dictionary table or system table, specify the schema name MASTER.
• If an archivable multi-chunk table is specified, accesses to the location table and system table (STATUS_CHUNKS) occur. At this time, locked resources are secured for the system table (STATUS_CHUNKS). For details about locks, see Locking in the HADB Setup and Operation Guide.

[AS] correlation-name:

Specifies a name assigned to separately identify a table for one of the following purposes:

• To join a table to itself
• To reference a column of the same table inside a subquery

table-nameは文字通りデータを取ってこようとするテーブル名のことで、[AS] correlation-nameっていうのテーブルを識別するためのもので、は自己結合したりサブクエリの中で同じテーブルのカラムを参照するのに使うと書かれています。[AS]というからにはASは省略できるってことなのでしょう。

結論

    LEFT OUTER JOIN
        `assets` `assets_bar`
    ON  `assets_bar`.`id` = `number_indices`.`asset_id`

    LEFT OUTER JOIN
        `assets` AS `assets_bar`
    ON  `assets_bar`.`id` = `number_indices`.`asset_id`

この2つのSQLは等価であり、assets_barというのはON以下で指定した条件でassetsテーブルをLEFT OUTER JOINしたときのテーブルを識別する別名であるということがいえます。

別名ですから本当にそういう名前のテーブルが有るってわけじゃないので見つからないのも当然なのでした。