背景
現場で見かけてもやもやしていた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-SQL（MicrosoftによるSQLの規格）のドキュメント。
SQL Server 2008 から記述が短くなったもの の紹介 - お だ のスペース
個人ブログ。テーブル値構成子の使い方が例示されており、分かりやすい！
i am BEST : VALUES コンストラクタは必ずしも INSERT INTO とだけ用いられるわけではない!?
上に同じく。
sqlのwhere in って、複数条件(カラム)を指定できるんですね - end0tknrのkipple - web写経開発
上に同じく。
