SQL練習室
← SQLガイド

SQLのINSERT … SELECTとは?別の表の検索結果をまとめて追加する書き方

INSERT … SELECTは、SELECTで取り出した行をそのまま別の表へ追加するSQLです。注文を保管用の表へコピーする、集計結果を別の表に残す、まだ登録されていないものだけを足す、といった作業を1文で行えます。VALUESで1行ずつ値を書く方法との違いは、追加する値をクエリの結果から作る点です。

例で使う表

ordersは注文1件が1行で、idは注文番号、customerは顧客名、amountは金額(円)、statusは支払状態(paid=支払済み、cancelled=取消)です。order_archiveは注文の保管先で、はじめは空です。archived_byは省略するとsystemが入ります。customer_salesは顧客別の売上の記録、customersは登録済みの顧客名の一覧です。各節では実行前の状態から追加し、追加先の表を確認用のSELECTで表示します。

orders:注文5件
idcustomeramountstatus
1佐藤3000paid
2鈴木1200cancelled
3佐藤4500paid
4田中800paid
5鈴木2000paid
order_archive:注文の保管先(空)
idcustomeramountstatusarchived_by

結果は0行です。

customer_sales:顧客別の売上記録1件
customertotalorder_count
高橋50002
customers:登録済みの顧客1件
name
佐藤

INSERT … SELECTの基本構文

sql
INSERT INTO order_archive (id, customer, amount, status)
SELECT id, customer, amount, status
FROM orders;

INSERT … SELECTで別の表へ検索結果をコピーする

INSERT INTO 追加先 (列名のリスト) の後ろに、VALUESの代わりにSELECTを書きます。SELECTが返した行がすべて追加先に入るので、ここではordersの5行がorder_archiveに5行追加されます。元のordersは変わりません。追加された内容はSELECTで確認します。

sql
INSERT INTO order_archive (id, customer, amount, status)
SELECT id, customer, amount, status
FROM orders;

SELECT * FROM order_archive ORDER BY id;
実行結果
idcustomeramountstatusarchived_by
1佐藤3000paidsystem
2鈴木1200cancelledsystem
3佐藤4500paidsystem
4田中800paidsystem
5鈴木2000paidsystem

SELECTの列は列名リストと左から順に対応する

列は名前ではなく位置で対応します。INSERT側の列名リストが (customer, id, amount, status, archived_by) なら、SELECT側も同じ順に customer, id, amount, status, 追加したい値 と並べます。固定の文字列や計算式もSELECTに書けるので、archived_byには「月次バッチ」を入れています。列名リストに書かない列は既定値になります。

sql
INSERT INTO order_archive (customer, id, amount, status, archived_by)
SELECT customer, id, amount, status, '月次バッチ'
FROM orders;

SELECT * FROM order_archive ORDER BY id;
実行結果
idcustomeramountstatusarchived_by
1佐藤3000paid月次バッチ
2鈴木1200cancelled月次バッチ
3佐藤4500paid月次バッチ
4田中800paid月次バッチ
5鈴木2000paid月次バッチ

WHEREで絞り込んだ行だけを追加する

SELECTには通常どおりWHEREを付けられます。追加されるのは条件に合う行だけです。ここでは支払済み(paid)の4件だけをコピーし、取消のid 2は追加されません。条件を付け忘れると取消の注文まで保管先に入ります。

sql
INSERT INTO order_archive (id, customer, amount, status)
SELECT id, customer, amount, status
FROM orders
WHERE status = 'paid';

SELECT * FROM order_archive ORDER BY id;
実行結果
idcustomeramountstatusarchived_by
1佐藤3000paidsystem
3佐藤4500paidsystem
4田中800paidsystem
5鈴木2000paidsystem

集計した結果を別の表に保存する

GROUP BYで集計したSELECTも、そのまま追加元にできます。支払済みの注文を顧客ごとに合計し、顧客名・合計金額・件数を customer_sales に保存します。SUMはbigintを返しますが、integerの列にはそのまま入ります。結果は最初から入っていた高橋の行と合わせて4行になります。

sql
INSERT INTO customer_sales (customer, total, order_count)
SELECT customer, SUM(amount), COUNT(*)
FROM orders
WHERE status = 'paid'
GROUP BY customer;

SELECT * FROM customer_sales ORDER BY total;
実行結果
customertotalorder_count
田中8001
鈴木20001
高橋50002
佐藤75002

NOT EXISTSで登録済みを除いて重複させない

customersの主キーは名前なので、同じ名前をもう一度入れるとエラーになり、文全体が取り消されます。登録済みの佐藤を除くには、追加元でNOT EXISTSを使います。ordersには佐藤と鈴木が2回ずつ出てくるため、DISTINCTで1人1行にそろえるのも必要です。結果は、まだ無かった田中と鈴木だけが増えて3人になります。

sql
INSERT INTO customers (name)
SELECT DISTINCT o.customer
FROM orders AS o
WHERE NOT EXISTS (
  SELECT 1
  FROM customers AS c
  WHERE c.name = o.customer
);

SELECT * FROM customers ORDER BY name;
実行結果
name
佐藤
田中
鈴木

INSERT … SELECTでよくある間違い

同じ型の列の順番を取り違えると、エラーにならず別の列へ入る

SELECTの列は位置で対応するため、customerとstatusのように同じ型(text)の列を入れ替えても実行できてしまいます。下の例ではstatusの値(paid・cancelled)が顧客名の列に入ります。型が違う場合はエラーになりますが、同じ型だと間違いに気づけません。列名リストとSELECTの並びは、必ず目で見比べて確認します。

sql
INSERT INTO order_archive (id, customer, amount, status)
SELECT id, status, amount, customer
FROM orders;

SELECT * FROM order_archive ORDER BY id;

実際に1問解いてみる

INSERT SELECT

ordersの全行を、id・customer・amount・statusの4列で order_archive にコピーしてください。

テーブル

読み込み中…

実行結果

関連構文

INSERT … SELECTについてよくある質問

INSERT … SELECTは「コピー」ですか?「移動」ですか?
コピーです。追加元の行はそのまま残ります。保管表へ移して元の表から消したいときは、追加のあとに同じ条件のDELETEを実行します。PostgreSQLでは、DELETE … RETURNINGの結果をWITHで受けてINSERTへ渡し、1文で移すこともできます。
SELECTの結果が0行のときはどうなりますか?
エラーにはならず、0行が追加されるだけです。WHEREの条件が厳しすぎたときも同じなので、追加件数が想定どおりかは確認用のSELECTで確かめます。
SELECTの列数が追加先の列名リストと合わないとどうなりますか?
SELECTの列が列名リストより多いとエラーになります。列名リストより少ない場合も、値が足りないのでエラーになります。どちらも列名リストと同じ数にそろえます。列名リストを書かないときだけ、先頭の列から順に対応して残りは既定値になります。
新しい表を作ってそこへ入れたいときも使えますか?
INSERT … SELECTは既にある表へ行を追加する構文です。結果をもとに表そのものを作りたいときは、別の構文であるCREATE TABLE ASを使います。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。