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で表示します。
| id | customer | amount | status |
|---|---|---|---|
| 1 | 佐藤 | 3000 | paid |
| 2 | 鈴木 | 1200 | cancelled |
| 3 | 佐藤 | 4500 | paid |
| 4 | 田中 | 800 | paid |
| 5 | 鈴木 | 2000 | paid |
| id | customer | amount | status | archived_by |
|---|
結果は0行です。
| customer | total | order_count |
|---|---|---|
| 高橋 | 5000 | 2 |
| name |
|---|
| 佐藤 |
INSERT … SELECTの基本構文
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で確認します。
INSERT INTO order_archive (id, customer, amount, status)
SELECT id, customer, amount, status
FROM orders;
SELECT * FROM order_archive ORDER BY id;| id | customer | amount | status | archived_by |
|---|---|---|---|---|
| 1 | 佐藤 | 3000 | paid | system |
| 2 | 鈴木 | 1200 | cancelled | system |
| 3 | 佐藤 | 4500 | paid | system |
| 4 | 田中 | 800 | paid | system |
| 5 | 鈴木 | 2000 | paid | system |
SELECTの列は列名リストと左から順に対応する
列は名前ではなく位置で対応します。INSERT側の列名リストが (customer, id, amount, status, archived_by) なら、SELECT側も同じ順に customer, id, amount, status, 追加したい値 と並べます。固定の文字列や計算式もSELECTに書けるので、archived_byには「月次バッチ」を入れています。列名リストに書かない列は既定値になります。
INSERT INTO order_archive (customer, id, amount, status, archived_by)
SELECT customer, id, amount, status, '月次バッチ'
FROM orders;
SELECT * FROM order_archive ORDER BY id;| id | customer | amount | status | archived_by |
|---|---|---|---|---|
| 1 | 佐藤 | 3000 | paid | 月次バッチ |
| 2 | 鈴木 | 1200 | cancelled | 月次バッチ |
| 3 | 佐藤 | 4500 | paid | 月次バッチ |
| 4 | 田中 | 800 | paid | 月次バッチ |
| 5 | 鈴木 | 2000 | paid | 月次バッチ |
WHEREで絞り込んだ行だけを追加する
SELECTには通常どおりWHEREを付けられます。追加されるのは条件に合う行だけです。ここでは支払済み(paid)の4件だけをコピーし、取消のid 2は追加されません。条件を付け忘れると取消の注文まで保管先に入ります。
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;| id | customer | amount | status | archived_by |
|---|---|---|---|---|
| 1 | 佐藤 | 3000 | paid | system |
| 3 | 佐藤 | 4500 | paid | system |
| 4 | 田中 | 800 | paid | system |
| 5 | 鈴木 | 2000 | paid | system |
集計した結果を別の表に保存する
GROUP BYで集計したSELECTも、そのまま追加元にできます。支払済みの注文を顧客ごとに合計し、顧客名・合計金額・件数を customer_sales に保存します。SUMはbigintを返しますが、integerの列にはそのまま入ります。結果は最初から入っていた高橋の行と合わせて4行になります。
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;| customer | total | order_count |
|---|---|---|
| 田中 | 800 | 1 |
| 鈴木 | 2000 | 1 |
| 高橋 | 5000 | 2 |
| 佐藤 | 7500 | 2 |
NOT EXISTSで登録済みを除いて重複させない
customersの主キーは名前なので、同じ名前をもう一度入れるとエラーになり、文全体が取り消されます。登録済みの佐藤を除くには、追加元でNOT EXISTSを使います。ordersには佐藤と鈴木が2回ずつ出てくるため、DISTINCTで1人1行にそろえるのも必要です。結果は、まだ無かった田中と鈴木だけが増えて3人になります。
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の並びは、必ず目で見比べて確認します。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:INSERT — クエリで追加する書き方、列名リストと値の対応、省略した列の既定値、ON CONFLICTを確認しました。
- PostgreSQL公式:データの挿入 — 表へ行を追加する基本と、クエリ結果で追加できることを確認しました。