初心者のためのSQLガイド🐘 : SQLを直感的に理解しよう

SELECT * WHERE a=b FROM c？　それともSELECT WHERE a=b FROM c ON *？

もしあなたが私のようなプログラマだったら、SQLは、初めは優しく見える言語の1
つかもしれません（ただ単に普通の英語通り読めばいいですから）。ですが、何かしらの理由で、なんてことのないクエリにもいちいち正しいシンタックスをググらなければいけないでしょう。
いずれJOINにAGGREGTATION、サブクエリにたどり着くでしょうが、読んだとしてもさっぱりでしょう。例えば次のような感じです。

SELECT members.firstname || ' ' || members.lastname
AS "Full Name"
FROM borrowings
JOIN members
ON members.memberid=borrowings.memberid
JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
  FROM books
  WHERE stock>(SELECT avg(stock)
    FROM books))
GROUP BY members.firstname, members.lastname;

うわっ！　これを見たらどんな初心者も震え上がるでしょうし、もしかしたら初めてSQLを見る、それなりの経験のある開発者でさえビビッてしまうかもしれません。実際はこうである必要はないのです。

直観的なものを覚えるのは、常に簡単です。このガイドを通して、SQL初心者の皆さんやSQLを使って仕事をしているけれども、新鮮な視点が欲しい皆さんが、SQLに対する壁を取っ払うことができればなと思っています。

この投稿で使用したすべてのクエリはPostgreSQL用ですが、データベース全体でSQL構文は似ているので、ここで紹介するクエリの中には他のSQLデータベースでも、私のSQL上と同じように動くものもあるはずです。

目次

1. 3つの魔法の言葉
2. データベース
3. シンプルなクエリ
   1. FROM データはどこから取得する？
   2. WHERE 何のデータを見せるべき？
   3. SELECT どのようにデータを見せるべき？
4. 結合
5. 集約
6. サブクエリ
   1. 2次元テーブル
   2. 1次元配列
   3. 単一の値
7. 書き込み操作
   1. 更新
   2. 削除
   3. 挿入
8. フィードバック

1. 3つの魔法の言葉

SQLではたくさんのキーワードが使われていますが、SELECT、FROM、WHEREはどのクエリにも登場するであろうワードです。先を読み進めれば、このキーワードがデータベースをクエリする上で最も基礎的な面を担っていることに頷けるでしょうし、他の、もっと複雑なクエリも、このキーワードの延長線上であるというのが分かるはずです。

2. データベース

この記事でこれ以降使うサンプルデータを見ていきましょう。

図書館があって、蔵書と会員がいます。別のテーブルには貸し出し者用のデータがあります。

• “books”のテーブルには本のタイトル、著者、出版年そして貸出可能冊数のデータがあります。とても分かりやすいデータです。
• “members”テーブルには登録メンバーの名字と名前のみ載っています。
• “borrowings”テーブルには会員が借りた本の情報が載っています。bookid列は借りられた”books”テーブルの本のIDが、memberid列には本を借りた”members”テーブルの会員と対応するようになっています。また本の貸し出し日と返却の予定日のデータもあります。

3.シンプルなクエリ

最初のクエリから始めてみましょう。著者が”Dan Brown”である本の全てのタイトルとIDが知りたいとします。

以下のようなクエリになるでしょう。

SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';

結果は以下のようになります。

id	title
2	The Lost Symbol
4	Inferno

簡単ですね。それでは実際に何が起きたか理解するために、クエリを細かく見ていきましょう。

3. 1 FROM : データはどこから取得する？

現段階でははっきりしているかもしれませんが、これから結合とサブクエリに触れる際に、とても重要になってきます。FROMは、テーブル、つまりデータを探さなければならない場所を問い合わす役割を果たします。このテーブルは、単にすでに存在する（前の例のような）もの、あるいは結合、サブクエリを通して作成したテーブルであるはずです。

3.2 WHERE : 何のデータを見せるべき？

WHEREは私たちが表示したい行を抽出するという、とてもシンプルな命令をしてくれます。今回の例の場合では、author列の値が”Dan Brown”である行だけ考慮すればいいわけです。

3.3 SELECT : どのようにデータを見せるべき？

探しているテーブルからお目当ての行にたどり着いたら、次は得たデータからまさに欲しいデータを取り出すにはどうしたらいいかということです。今回の場合では、本のタイトルとIDが知りたいわけです。そこでSELECTを使います。ASを使って表示したいカラムの名前を付け替えることもできます。

最後に簡単な図として、これまでのクエリを表しておきます。

4.結合

次は”Dan Brown”の（特定の本ではなく）全ての著書の中から、貸し出しがあった本のタイトルと貸出期間を表示させましょう。

SELECT books.title AS "Title", borrowings.returndate AS "Return Date"
FROM borrowings JOIN books ON borrowings.bookid=books.bookid
WHERE books.author='Dan Brown';

結果は以下のようになります。

Title	Return Date
The Lost Symbol	2016-03-23 00:00:00
Inferno	2016-04-13 00:00:00
The Lost Symbol	2016-04-19 00:00:00

クエリの大部分は先ほどの例と似ていますが、FROM句が異なります。つまり、クエリで参照するテーブルが変わったということです。今回は”books”テーブルや”borrowings”テーブルではなく、この2つを結合した新たなテーブルに対してクエリを実行します。

borrowings JOIN books ON borrowings.bookid=books.bookidの部分は、”books”テーブルと”borrowings”テーブルそれぞれのbookidが一致した場合に、これら2つのテーブルに含まれるエントリを全て結合し、別のテーブルを作成するという意味です。以下は、結合したテーブルです。

このテーブルから、先ほどの例と同様にデータを取得します。このようにテーブルを結合する際は、必ず結合方法に配慮するようにしましょう。そうすると、あとは上述の”シンプルなクエリ”のレベルまでクエリを簡略化することができます。

では2つのテーブルをもう少し複雑な方法で結合してみましょう。

今回は”Dan Brown”の著書を借りた人全員の氏名をフルネームで表示します。

では、これをボトムアップ手法で実現してみましょう。

• ステップ1：データの取得元を指定します。欲しい結果を取得するためには、”books”テーブルと同様に、”member”テーブルも”borrowings”テーブルに結合しなければなりません。よって、クエリのJOIN句は以下のようになります。

borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid

以下は結合したテーブルです。

• ステップ2：表示するデータを指定します。今回は著者が”Dan Brown”の場合のみを対象とします。

WHERE books.author='Dan Brown'

• ステップ3：データの表示方法を指定します。必要なデータを抽出したら、次は該当する本を借りた人の氏名をフルネームで表示します。

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"

すばらしいですね。最後に上の3つの要素を組み合わせれば、以下のクエリの完成です。

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown';

結果は以下のようになります。

First Name	Last Name
Mike	Willis
Ellen	Horton
Ellen	Horton

見事ですね。ただ同じ名前が重複しているので、この点は少し改善しましょう。

5. 集約

一言で表現すると、集約とは複数行を1行にまとめるための手法です。集約のパターンによって、各列に適用するロジックだけが異なります。

さて、先ほどの例ではクエリ結果の中に重複したデータがありました。Ellen Hortonは本を複数冊借りていますが、この情報を先ほどのように表示するのはベストとは言えません。では、クエリを次のように書いてみましょう。

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;

結果は以下のようになります。

First Name	Last Name	Number of books borrowed
Mike	Willis	1
Ellen	Horton	2

集約には、大抵GROUP BY句を使います。これによりテーブル、またはクエリで返されたテーブルをグループ分けできます。各グループは、GROUP BY句で指定した列の固有値（または値の集合）に対応しています。
今回は、先ほどの例で取得した結果を行のグループに変換します。さらにcountで集約を行い、複数行を1つの値（この例では行数）に変換します。つまり、この値は各グループから得られる値ということです。

結果の各行は、各グループの集約結果を表しています。

論理的に言えば、結果として表示するフィールドは全てGROUP BY句で指定するか、集約する必要があるということになります。他のフィールドは全て行指向型に変わってしまうので、SELECTされても、どの値を取得すべきか判別できないからです。

上の例では（行数をカウントするだけでよいので）COUNT関数を全ての行に適用しています。一方、その他のsumやmaxなどの関数は、特定の列にのみ作用します。例えば著者別の全蔵書数を知りたい場合は、以下のクエリを使用します。

SELECT author, sum(stock)
FROM books
GROUP BY author;

結果は以下のようになります。

author	sum
Robin Sharma	4
Dan Brown	6
John Green	3
Amish Tripathi	2

ここではstock列にのみsum関数が適用され、グループごとに全ての値が合計されています。

6.サブクエリ

サブクエリは通常のSQLクエリを、大きなクエリの中に組み込んだものです。

サブクエリは、返す結果によって以下の3つのタイプに分かれます。

6.1 2次元テーブル

これは複数列を返すクエリで、その良い例が先ほど集約の演習で扱ったクエリです。サブクエリを使うと単純に、さらにクエリをかけた別のテーブルが返されます。先ほどの例で”Robin Sharma”の著書の蔵書数だけを取得するには、以下のようにサブクエリを使うこともできます。

SELECT *
FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
WHERE author='Robin Sharma';

結果は以下のようになります。

author	sum
Robin Sharma	4

6.2 1次元配列

単一カラムから成る複数行を返すクエリは、配列や2次元テーブルと同じように使用できます。

例として、蔵書数の合計が3冊を超える著者について、全ての本のタイトルとIDを取得してみましょう。この処理は以下の2ステップに分かれます。

1. 蔵書数の合計が3冊を超える著者リストを取得します。先ほどの例を利用すると、以下のように書けます。

SELECT author
FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
WHERE sum > 3;

結果は以下のようになります。

author
Robin Sharma
Dan Brown

これは['Robin Sharma', 'Dan Brown']とも書けます。

2. この結果を次のクエリに利用します。

SELECT title, bookid
FROM books
WHERE author IN (SELECT author
  FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
  WHERE sum > 3);

結果は以下のようになります。

title	bookid
The Lost Symbol	2
Who Will Cry When You Die?	3
Inferno	4

これは次のように書いても同じです。

SELECT title, bookid
FROM books
WHERE author IN ('Robin Sharma', 'Dan Brown');

6.3 単一の値

これは1行1列の結果を返すクエリです。定数として使用できるため、比較演算子のように値を使用する箇所であればどこでも使用できます。さらに要素を1つしか持たない2次元テーブルや配列と同じように使用できます。

例として、蔵書数がその平均値よりも多い本の情報を取得してみましょう。

蔵書数の平均値は以下で取得できます。

select avg(stock) from books;

結果は以下のようになります。

avg
3.000

これはスカラー値の3と同じように使用できます。

よって最終的なクエリは次のようになります。

SELECT *
FROM books
WHERE stock>(SELECT avg(stock) FROM books);

これは次のように書いても同じです。

SELECT *
FROM books
WHERE stock>3.000

結果は以下のようになります。

bookid	title	author	published	stock
3	Who Will Cry When You Die?	Robin Sharma	2006-06-15 00:00:00	4

7.書き込み操作

データベースの書き込み操作は、複雑な読み取り用のクエリに比べると格段にシンプルです。

7.1 更新

UPDATE構文は、意味としては読み取り用のクエリに似ています。唯一の違いは、一式の行の中から列をSELECTする代わりに、列をSETするという点です。

例えば、突如”Dan Brown”の本が全てなくなったので、蔵書数を0に更新したいとしましょう。この処理は以下のように書けます。

UPDATE books
SET stock=0
WHERE author='Dan Brown';

WHEREの部分は、ここでも行を選択するという処理になります。読み取りクエリの時はSELECTを使っていましたが、今回はSETを使います。列名に加えて、選択された列に新たに設定する値を指定します。

7.2 削除

DELETEは、単純にSELECTやUPDATEクエリの列名がないものです。WHERE句の処理はSELECTやUPDATEと同じで、選択した列を削除します。DELETEは行ごと削除するので、列名を指定する必要はありません。では、蔵書数を0に更新する代わりに、以下のようにDan Brownのエントリを全て削除してしまいましょう。

DELETE FROM books
WHERE author='Dan Brown';

7.3 挿入

他のクエリと唯一異なっているのが、INSERTクエリです。以下はそのフォーマットです。

INSERT INTO x
  (a,b,c)
VALUES
  (x, y, z);

a, b, cは列名で、x, y, zは各列に挿入する値です。値は指定した順番通りに挿入されます。INSERTの説明はそれくらいでしょう。

以下により具体的なサンプルを示します。これはbooksテーブルの全データをINSTERTするクエリです。

INSERT INTO books
  (bookid,title,author,published,stock)
VALUES
  (1,'Scion of Ikshvaku','Amish Tripathi','06-22-2015',2),
  (2,'The Lost Symbol','Dan Brown','07-22-2010',3),
  (3,'Who Will Cry When You Die?','Robin Sharma','06-15-2006',4),
  (4,'Inferno','Dan Brown','05-05-2014',3),
  (5,'The Fault in our Stars','John Green','01-03-2015',3);

8. フィードバック

本ガイドも最後まできたので、ここで簡単なテストに挑戦してみましょう。この記事の冒頭で紹介したクエリを見てください。さて、このクエリはどのような処理を行っているのでしょうか？　SELECT、FROM、WHERE、GROUP BY、そしてサブクエリのコンポーネントに分解して考えてみてください。

以下に、もう少し読みやすく書き直したものを載せます。

SELECT members.firstname || ' ' || members.lastname AS "Full Name"

FROM borrowings
JOIN members
ON members.memberid=borrowings.memberid
JOIN books
ON books.bookid=borrowings.bookid

WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>  (SELECT avg(stock) FROM books)  )

GROUP BY members.firstname, members.lastname;

このクエリでは、蔵書数の合計がその平均値よりも多い本を借りたメンバーのリストを取得しています。

結果は以下のようになります。

Full Name
Lida Tyler

難なく正答が分かったでしょうか？　分からなかった方もフィードバックやコメントをお寄せください。いただいたご意見を参考に、本記事を改善していきます。お読みいただきありがとうございました！