INSERT文(SQLを基本から学ぶシリーズ)

 2021.04.19  株式会社システムインテグレータ

SQLにおけるINSERT文は「どのようなデータをどのテーブルに登録するか」を記述したものです。

「どのようなデータを」の部分がポイントです。登録する内容は、0,1...'a', 'あいうえお'などの固定値だけでなく、他のテーブルのデータをそのまま持ってきたり、値によってCASE文を書くことだって可能です!柔軟な記述が可能なのです。 

本記事ではINSERT文の基本構文から始まり、CASE文やテーブル結合を活用したINSERTそれらにまつわるよくあるエラーアンチパターンまで解説します。 

本記事に出てくるSQLは、MySQLを使って動作検証をしています。SQL前には、そのSQLの仕様(何をどこへ登録したいのか)を書いておりますので、仕様とSQLを比べて見て理解を深めてください。 

INSERT文の基本

まずはINSERT文の基本からですデータ登録先となるテーブルの、列名を指定する方法です。 

仕様:テーブルデータ登録する名指定) 

INSERT INTO テーブル名 (列名1, 列名2,...) VALUES (値1, 値2,...);

テーブル名のあとにカッコで、列名を並べます。VALUESのあとのカッコに、登録したい値をカンマで区切って並べます。列名と値は順番を合わせる必要があります。上記の例では、列名と値は次のように対比しています。

列名1は値1
列名2は値2
...

 一方、テーブルに定義された列名の順番に、VALUESのカッコの中に確実にデータを並べることができるのなら、列名は省略可能です。

仕様:テーブルにデータを登録する(列名省略)

INSERT INTO テーブル名 VALUES (値1, 値2,...);

となり、上のINSERT 文はシンプルで便利なように見えますが、思わぬトラブルが起きることがあります。現場でも省略した形はあまり見ません。詳しくは、後のアンチパターン例で詳しく解説します。

INSERTCASE文が使える

INSERTにおけるVALUES句に注目しましょう。

1,2, ... と続いています。ここは、要は「値」であれば問題ありません。ところでCASE WHEN構文を覚えていますか?CASE WHENは複雑な形に見えますが、最終的には単一の値を返します。

MySQLには、日付を与えれば曜日の番号0, 1, ...0は日曜、1は月曜...)を返すDAYOFWEEKという関数があります。これを数値ではなく曜日の日本語「日曜日、月曜日、...」に変換してINSERTしてみましょう。

このケースについて書き方を確認してみましょう。
まずは普通に曜日を返すSELECT文を作ります。すると次のようになります。

仕様:現在日付から曜日を取得する(だけ)

select
(
  CASE dayofweek(now())
    WHEN 1 THEN '日曜日'
    WHEN 2 THEN '月曜日'
    WHEN 3 THEN '火曜日'
    WHEN 4 THEN '水曜日'
    WHEN 5 THEN '木曜日'
    WHEN 6 THEN '金曜日'
    WHEN 7 THEN '土曜日'
  END
);

実行した日の曜日が日本語で返ってくれば成功です。ちなみに本記事の執筆当日は水曜日だったので、結果は「水曜日」と表示されました。

このSQLを使って、INSERT文を書いてみましょう。対象となるテーブルは、ID(自動採番なのでSQLには出てきません)と曜日(youbi)だけのシンプルなテーブルsampleです。

仕様:現在日付から曜日を取得して登録する

INSERT INTO sample (youbi) VALUES (
CASE dayofweek(now())
  WHEN 1 THEN '日曜日'
  WHEN 2 THEN '月曜日'
  WHEN 3 THEN '火曜日'
  WHEN 4 THEN '水曜日'
  WHEN 5 THEN '木曜日'
  WHEN 6 THEN '金曜日'
  WHEN 7 THEN '土曜日'
END
);

youbiという列に、実行した日の曜日を日本語で登録しています。複雑に見えますが、CASE文の返す値をそのままINSERTしているだけです。

dayofweekは曜日を1(日曜日)〜7(土曜日)で返却します。nowは現在日付を取得する関数です。これらはMySQL特有のものであり、お使いのDBごとに関数が異なりますので注意してください。

 INSERT文の応用

 前章で、INSERT文は対象のテーブルと列名、値を指定するのが基本と解説しました。「値」というのがミソで、値であればSQL文の結果だってINSERT文に使えます。

本章では、さらに一歩進めて応用的なINSERT文を解説します。

INSERTする内容をSQLで取得(クエリ)

サブクエリ(副問合せ)を使えば、SQLで取得した結果を別のテーブルにINSERTすることができます。例えば、ある社員はemp_no10106、彼の上司のemp_no10001であるとしましょう。この社員をテーブルemployeesに登録する場合は、以下のようになります。

仕様: emp_no10106の社員(彼の上司のemp_no 10001である)をemployeesに登録する

INSERT INTO employees (emp_no, mgr_name) VALUES (
'10106',
(SELECT CONCAT(first_name, last_name) FROM employees
  WHERE emp_no = '10001')
);

VALUES句にSELECT文を丸ごと入れられるとは驚きですね。INSERTのカッコの中にある列名mgr_nameと、SELECT文の結果であるfirst_namelast_nameを連結したものが対比しています。

INSERTする内容をSQLで取得

SELECTした内容を、そのままINSERTすることもできます。employeesSELECTして、その結果をemployees2というテーブルに登録する場合、以下のようになります。

仕様:employeesテーブルの内容を、employees2テーブルに登録(全件)

INSERT INTO employees2 (emp_no, birth_date)
SELECT emp_no, birth_date from employees;

もちろん、SELECT文はWHERE等で条件指定することもできます。

テーブル結合した結果をINSERTする

INSERT文は、テーブル結合をしたものを他のテーブルにINSERTすることもできてしまいます。

INSERT文とSELECT文における結合の両方を、一度に考えるのはちょっとむずかしいです。よって「 INSERTCASE文が使える」でお伝えしたコツを使いましょう。まずは普通にSELECT文を考えます。

従業員と所属する部署名を求めます。

仕様:従業員の所属テーブル(dept_emp)と部署テーブル(departments)を結合

SELECT
de.emp_no,
dp.dept_name
FROM dept_emp de
JOIN departments dp
ON de.dept_no = dp.dept_no

dept_empは従業員がどこに所属しているのかを保持するテーブル、departmentsは部署の情報を持つテーブルです。
これをそのままINSERT文に書くだけです。次のようになります。

仕様:従業員の所属情報から従業員マスタを生成する。

INSERT INTO employees (emp_no, dept_name)
SELECT
de.emp_no,
dp.dept_name
FROM dept_emp de
JOIN departments dp

INSERT文のカッコの中と、SELECT句の中が対応しているのが分かると思います。

INSERT文のアンチパターン

INSERT文には便利な書き方があることを解説してきました。しかし気をつけないとエラーの原因を見つけるのがむずかしくなったり、メンテナンス性が下がる場合があります。

INSERT文のアンチパターンを見ていきましょう。

INSERTの列名を省略する

SELECTした結果をそのままINSERTする場合、次のようなSQLを書くことが可能です。

INSERT INTO employees2
SELECT * FROM employees

INSERT INTO employees2の次にカッコ()がありません。SELECT句も*になっていています。つまり列名を明記していません。
実は2つのテーブル間の列名の定義がまったく同じなら、列名は省略できるのです。省略しても、内部的に全ての列名を書いたように動作します。

一見楽に見えますが、これがエラーの原因となります。テーブルの列は、システムの仕様変更により追加や削除が行われます。ということは、列名や順番がすべて同じである前提が崩れます。INSERTVALUES(1, 2, ...)と、SELECT 1, 2, ... の順番が合わなくなるということです。

この問題を防ぐために、基本的には列名を省略せずに明示的に記述するようにしましょう。

クエリを含んだINSERT文が失敗する

クエリを含んだINSERT文が失敗することがあります。例えば先に出てきたSQLをもう一度見てみます。

INSERT INTO employees (emp_no, mgr_name) VALUES (
'10106',
(SELECT CONCAT(first_name, last_name) FROM employees
  WHERE emp_no = '10001')
);

上記のSQLを実行すると、employeesテーブルには(10106, null)というデータが入ったとしましょう。これはなぜだか分かりますか?

理由は、SELECTの部分だけ実行してみると分かります。(10106, null)というデータが入った場合、

SELECT CONCAT(first_name, last_name) FROM employees
  WHERE emp_no = '10001'

このSQLの結果が何も返ってこないはずです。クエリやテーブル結合した結果を使ってINSERTする場合、まずはINSERTしたい内容、つまり上のSQL文が意図した結果を得られているかを調べる必要があります。

本記事で「まずは最初にSELECT文を作る」と言ってきたのはこれが理由です。クエリやテーブル結合に失敗してINSERTができない、これはとてもよくあるエラーです。もし発生したら、部分的に実行してみて問題ないか確認するとよいでしょう。 

まとめ

本記事ではINSERT文を解説しました。解説した内容をじっくり読み込んで理解し、効率的なINSERT文が書けるように頑張ってくださいね!


RELATED POST関連記事


RECENT POST「SQL」の最新記事


この記事が気に入ったらいいねしよう!

om