行値構成子とテーブル値構成子:なぜSELECT結果を直接INSERTしたり、WHERE句で複数列を同時に比較できるのか

背景

現場で見かけてもやもやしていたSQLに、次のようなものがある。

INSERT INTO my_db.animals SELECT * FROM your_db.animals; --例1.

SELECT結果を直接INSERTしている。INSERT文と言えば INSERT INTO xxx VALUES xxx の形式だと思っていたので驚いた。
同時に、今までINSERT文の文法だと思っていたものが全てじゃないような気がしてきた。

こんな例もある。

SELECT * FROM my_db.animals WHERE (name, sound) = ('Cat', 'Myao'); --例2.
SELECT * FROM my_db.animals WHERE (name, sound) IN SELECT name, sound FROM your_db.animals; --例3.

WHERE句の左辺が複数要素ある。これは便利だ。
もし左辺に1項目づつしか指定できなかったら、例3.では比べる対象の列を項目に合わせて分けるため、SELECTを2回発行することになってしまう。

どうして例1.〜 例3.のような文法が使えるのか、ちょっと調べてみた。

前提

SQLの言語仕様はISOで定められており、これに各ベンダーが準拠している。
歴史的に統一標準規格がなかった時期が長かったため、ベンダーによって準拠の度合いはまちまちなんだそうだ。(Wikipediaより)
したがって、ISO(制定年ごとにバージョンがある)があればそれを参照し、ISOのドキュメントがなかったりベンダー固有仕様ならベンダーのサポートサイトを参照しよう。

これから調べることは文法の基本っぽい雰囲気だし、何よりISOのドキュメントが見つかったのでそれを読んで理解しよう。

行値構成子とテーブル値構成子

ぶっちゃけ自分で考えたことで事実確認はしてないんだけど、どうして例1. 〜例3. の文法が使えるのかの結論から行こう。
- INSERTが値に取るのはテーブル型の無名オブジェクトと思われる。VALUESはテーブル型のオブジェクトを生成するコンストラクター(テーブル値構成子)にすぎない。
- WHERE句では左辺が行値構成子の場合、右辺に対応する列があるものとして要素ごとに評価する。

それでは、行値構成子とテーブル値構成子とは何なのか。
SQL92の規格によれば以下の通り。
※例示と訳語のチョイス、翻訳は私自身によるものです。


row value constructor(行値構成子、行値コンストラクタ、RVCとも。)
値のセットを行または行の一部を構成するようにするもの。

('Cat', 'Myao') -- 行値構成子
('Dog') -- これも行値構成子
'Lion', 'Gyaoo' -- ()で囲わない場合、行値構成子リスト
'Beer' -- 単体だと行値構成子エレメント

要するに、()で囲うとそこが行として見なされるわけだな。

table value constructor(テーブル構成子、テーブル値コンストラクター、TVCとも。)
行値構成子をテーブルを構成するようにするもの。

VALUES ('Cat', 'Myao'), ('Dog', 'Bao'), ('Bird', 'Piyo') -- テーブル構成子

ということは、こんなこともできるのである。

SELECT * FROM ( VALUES('Cat', 'Myao'), ('Dog', 'Bao'), ('Bird', 'Piyo') ); -- VALUESを使い、手打ちの値をテーブルに見立てる。  
VALUES(1); -- 1をSELECTで取得したテーブルと同じように見立てる。

いや〜知らなかった。

まとめ

INSERT文は必ずVALUESを使わなければいけないのではなく、値がテーブル型のオブジェクトなら良さそうだ。
こういう例は他にもありそうだから、SQLを使う時は何のオブジェクトを扱っているのか気にしてみよう。

参考文献

SQL - Wikipedia
そもそもSQLについて調べるにあたり、何を見ればいいか分からなかったので。

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Wikipediaのリンクから発見した、SQL 92の仕様書。カーネギーメロン大学のサーバーに置いてくれてあるようだ。

テーブル値コンス トラクター (TRANSACT-SQL) | Microsoft Docs
T-SQLMicrosoftによるSQLの規格)のドキュメント。

SQL Server 2008 から記述が短くなったもの の紹介 - お だ のスペース
個人ブログ。テーブル値構成子の使い方が例示されており、分かりやすい!

i am BEST : VALUES コンストラクタは必ずしも INSERT INTO とだけ用いられるわけではない!?
上に同じく。

sqlのwhere in って、複数条件(カラム)を指定できるんですね - end0tknrのkipple - web写経開発
上に同じく。