職業プログラマの休日出勤

職業プログラマによる日曜自宅プログラミングや思考実験の成果たち。リアル休日出勤が発生すると更新が滞りがちになる。記事の内容は個人の意見であり、所属している(いた)組織の意見ではない。

create table

SQLで、select文の結果を、新規に作成するテーブルへ突っ込む方法があります。きちんと構築するアプリケーションの中で使うことは稀ですが、ささっとバックアップを取得したり、手軽にデータ分析をやったりとか、そういった用途で本当によく使います。
先日、SQL Worldの お だ さんの記事「SELECT した結果を新しいテーブルに挿入する - お だ のスペース」を見ていて、実はRDBMSによってそのSQL文の文法が異なることに気付きました。

テスト用データ(PostgreSQL, MySQL, SQL Server どれでも実行可能)

create table test20131213 (
	id int not null primary key,
	data1 varchar(10)
);
insert into test20131213 values(1, 'a');
insert into test20131213 values(2, 'b');
insert into test20131213 values(3, 'c');

テーブル作成のSQL文その1

create table test20131213_2
as select * from test20131213;

僕が普段書くのは、この形式のSQL文です。
PostgreSQLMySQLは上手く動作しましたが、SQL Server では

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.

こんなエラーが帰ってきました。ありゃりゃ。

テーブル作成のSQL文その2

select * into test20131213_3 from test20131213;

冒頭で紹介した お だ さんの記事では、この形式のSQL文が紹介されていました。
当然ながら、SQL Serverでは動作します。PostgreSQLでも動作しました。
MySQLでは

#1327 - Undeclared variable: test20131213_3

というエラーメッセージが返されました。そう、select … into …文は、ストアドプログラムを書くときに、select文の結果を変数に代入するために使われるのです。

実行可否のまとめ

create table … as select … select … into …
PostgreSQL
SQL Server ×
MySQL ×

やっぱり、両方動くPostgreSQLを使うのが一番、ですね(無理矢理w)。

あと、真面目な話。ほとんどのRDBMSで(たぶん全部)、こうやって作成されたテーブルには主キー制約やindexが作成されないので、データ分析のSQL流して帰ってこない…という罠には注意しましょう。

どっちが良いの?

アンチ SQL Server という訳ではありませんが、本件に限った話をすると、select … into …文よりもcreate table … as select …文の方が優れていると考えています。何故ならば、文の先頭を見れば、単なるselect文なのか、それともcreate table文なのか判別することができるからです。一人でビシバシ使う分にはどっちでも良いんですけど、他人の書いたSQL文を本番環境で実行するときのことを考えれば、この観点は非常に重要です。(という話をし始めると、select … into …が動作しないMySQLの方が他のRDBMSよりも優れているじゃないか!という話になってしまうので、ここで止めておきますw)

ここで、PostgreSQL 9.3のマニュアルを引用しておきましょう。

The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This indeed is the usage found in ECPG (see Chapter 33) and PL/pgSQL (see Chapter 40). The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code.

http://www.postgresql.org/docs/9.3/interactive/sql-selectinto.html

大雑把な日本語訳をすると「SQL標準では、select into文はselect文の結果を変数に代入するためのものです。PostgreSQLでは互換性のために、この文にcreate tableの意味を持たせています。新規に書くコードでは、create table as文を使用するべきです」だそうです。なるほど。

動作確認に使用した環境

  • PostgreSQL 9.1.4(古くてゴメンなさい)
  • MySQL 5.1.69(めっちゃ古くてゴメンなさい)
  • Microsoft SQL Server 2012 (SP1)