SQL。言わずとしれたデータベース操作言語。今やプログラマー必修です。
SQLの中でも、最も重要、かつ難解なのが、SELECT文。
その構文、JavaやPythonといった他言語とは、明らかに異質です。
経験豊富なプログラマーでも、SQL SELECTを苦手としている人はけっこう多い。
理解するポイントはこれ。「SELECTは、表から表へ変換する操作」。
そしてその操作は、大きく４つの要素に分類されます。どのような操作なのか、見ていきましょうを解説。
SQL SELECTが持つ意味
SQLとは、データベース操作言語です。データベースに対する代表的な操作は以下。
- INSERT：挿入
- SELECT：検索
- UPDATE：更新
- DELETE：削除
このうち、一番重要なのが、SELECT（検索）です。
SELECTは「表形式のデータベースを操作する」
SELECTで検索する対象とするのは、表形式のデータです。
以下は表形式データの例。国の情報を管理するデータです。
国コード、国名、地域、のデータを管理する、countryテーブルです。
横一列が、１かたまりのデータ。日本、中国…といったデータですね。それぞれのデータが、国コードなどの情報を持ちます。
SELECTは「表から表に変換する」操作
SELECT操作は、一般的には「データベースの検索や抽出を行う」といった説明がなされます。
この説明、間違いではないですが、あまりイメージが湧きにくい説明です。
SELECTを理解するためには、このイメージが大事。
例をあげましょう。以下は「国の一覧表」を、「東アジアの国名表」に変換する、といった操作です。
この変換をSQLで書くと、こうなりますね。
SELECT name, region FROM country WHERE region = 'Eastern Asia'SELECTは、与えられた表を、別の表に変換するための操作。まずこのイメージを持っておきましょう。
SELECTで表を変換する４つの要素
ある表を、別の表に変換する。その操作は、次の４つの操作に分類できます。
- 取り出す 欲しい列を持ってくる
- 選択する 条件に合うデータだけ抽出する
- 計算する 列を計算してまとめる
- 結合する 複数の表を合わせる
複雑なSELECT文も、この４つの操作の組み合わせで構成されているのです。
４つの操作はそれぞれどういうものなのか、見ていきましょう。
「取り出す」：欲しい列を持ってくる
まずは基本。表の中から、欲しい列を「取り出す」。
SELECTに続けて、欲しい列を指定します。
SELECT name, region
FROM country
;
これは分かりやすいでしょう。
「選択する」：条件に合う行だけ抽出する
欲しいデータの条件を与えて、そのデータだけを「選択する」。
選択する条件はWHEREで指定します。
SELECT code, name, region
FROM country
WHERE region = 'Eastern Asia'
;
これも見た目で分かりやすいでしょう。
「計算する」：列を計算してまとめる
続いて「計算する」。集約関数と呼ばれる計算です。
- COUNT：個数を計算
- SUM：合計を計算
- MAX：最大値を計算
- MIN：最大値を計算
- AVG：平均値を計算
計算の意味は自明ですが、SQL的には、ちょっとイメージを変えておく必要があります。
集約関数は、「１列分の表を、１つのデータにまとめた表に、変換する」という操作と捉えましょう。
cityテーブルから、最大の人口を取り出す例です。
SELECT max(population)
FROM city
;「人口」という１列の表を、「最大人口」という１つのデータにまとめた表に、変換します。
「列を縮めて集約する」イメージですね。
ただ実際に使うときは、「国ごとに最大人口を集計する」ほうが使いやすいでしょう。
といって、単純にこう書いても、エラーになります。
SELECT countrycode, max(population)
FROM city
;
--ERROR: column "city.countrycode" must appear in the GROUP BY clause or be used in an aggregate functionここで使うのがGROUP BY。どの単位で集約するか指定します。
SELECT countrycode, max(population)
FROM city
GROUP BY countrycode
;
国ごとの集約した結果が、得られました。
「結合する」：複数の表を合わせる
SELECTの中で一番難しいのがこれ。２つの表を「結合する」。
以下の、countryテーブル、cityテーブル、この２つを結合してみましょう。
難しそうな感じがする結合操作。実は、イメージはけっこう単純。
２つの表の、すべてのデータを組み合わせて、１つの表にするのです。
結合した跡は、こんな感じの１つの表にまとまります。
すべての組み合わせを考えるので、結合してできる表は、けっこうデータ数がなります。
例えばcountryテーブルが200行、cityテーブルが500行あれば、結合後のテーブルは200 ✕ 500 = 100,0000行になります。
ただ、このような単純結合した表を、そのまま使うことはまずありません。
できた表を見ると、明らかに要らなそうなデータがあります。例えば、国名がJapanで、都市名がNewYork。こんなデータは意味がない。
こうみると、役に立つのは、codeが合っているデータだけですね
結合するときに、「codeが合っている」という条件を指定して、必要なデータだけ残るようにします。
以上をSELECT文で書いてみましょう。結合する表をINNER JOIN、条件をONで指定します。
SELECT country.name, city.name
FROM country
INNER JOIN city
ON country.code = city.countrycode
;
必要なデータだけ残った状態で、２つの表を結合できました。
高度なSELECTに挑戦しよう
SELECTに登場する、「取り出す」「選択する」「計算する」「結合する」の４つの操作。
この４つの操作を組み合わせることで、複雑な問い合わせにも対応することができます。
４つの操作をすべて使った、応用問題にチャレンジしてみましょう。
応用問題のお題
使う表は、先ほども出てきた、countryテーブルとcityテーブル。
このテーブルから、以下条件で検索します。
欲しい結果は、このような表になるはず。
この表を作り出す、SELECT文を作ります。
最初に、SELECT文の完成形をお見せしておきましょう。こんな感じになります。
SELECT country.name, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
WHERE country.region = 'Eastern Asia'
;だいぶ難解ですね。でも大丈夫。
４つの操作を、順番に紐解いていけば分かります。SELECT文が出来る過程を、見ていきましょう。
STEP1：最大の人口を計算する
まずはとにかく、最大人口を計算しないといけません。
「計算する」ですね。先ほども出てきたやつです。国ごとの最大人口を計算します。
SELECT countrycode, max(population)
FROM city
GROUP BY countrycode
;結果も、さきほどと同じです。
STEP2：最大の人口を持つ都市名を付け加える
これだとまだ、都市名が表示できていません。都市名も一緒に表示しましょう。
どうしようか。都市名が入っているのは、cityテーブルです。
こう考えてみましょう。
「先ほど計算した最大人口の表」と「cityの表」を、「結合する」
さきほどの表に、maxpopucityという名前をつけ、INNER JOINで結合します。
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucityINNER JOINで指定しているのが、「テーブル名」でなく、「SELECT文」であることに注目です。
SELECTが、「表から表に変換する操作」。だからSELECTでできた結果も「表」です。
なので、その「SELECTでできた表」を「他のテーブル」と結合する、ということもできるのです。
結合した表はこんなイメージになります。
結合条件は、国名コードが同じ、かつ、人口が同じ、です。
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu必要な行だけが結合されます。
これで、その国の最大人口を持つ都市、という表が得られました。
完成した表から、「選択する」で列を取り出しましょう。
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
SELECT文が、複雑になってきましたね。でも結果はいい感じ。
STEP3：国名を付け加える
つぎはこれに、国名も加えます。国名が入っているのは、countryテーブル。
要領は先ほどと同じですね。countryテーブルと、先ほどの表を、「結合する」。
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname結合条件は、国名コードが同じ、ですね
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode「取り出す」で列を取り出します。
SELECT country.name, country.region, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode結果、こうなります。
東アジアに絞る
最後の条件です。東アジア(Eastern Asia)のデータだけにします。
「選択する」ですね。WHEREで、regionを絞る条件を入れます。（また選択からは、regionを外しておきます）
SELECT country.name, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
WHERE country.region = 'Eastern Asia'
;
これで、最終結果の表になりました。
完成！
これで、SELECT文の完成です。
結果を改めて確認しましょう。
SELECT country.name, maxpopucityname.name, maxpopucityname.maxpopu
FROM country
INNER JOIN (
SELECT city.countrycode, city.name, maxpopucity.maxpopu
FROM city
INNER JOIN (
SELECT countrycode, max(population) AS maxpopu
FROM city
GROUP BY countrycode
) AS maxpopucity
ON city.countrycode = maxpopucity.countrycode
AND city.population = maxpopucity.maxpopu
) AS maxpopucityname
ON country.code = maxpopucityname.countrycode
WHERE country.region = 'Eastern Asia'
;
SELECT文は複雑ですが、４つの操作の組み合わせを考えると、表が変換される過程が見えてきますね。
注意：SELECTの書き方はひとつじゃない
応用問題を解いて、SELECT文を作りました。
ただ実は、答えとなるSELECT文は、これが唯一の答えではありません。
例えば、こう書いても、同じ表が出力できます。
SELECT name,
(SELECT name
FROM city
WHERE country.code = city.countrycode AND city.population = (
SELECT max(population)
FROM city
WHERE city.countrycode = country.code
)
) AS cityname
,
(SELECT max(population)
FROM city
WHERE country.code = city.countrycode
) AS maxpopu
FROM country
WHERE country.region='Eastern Asia'
;特に、「結合」を伴うSELECTの場合、これは覚えておきましょう。
- SELECTは、同じ結果が出すにも、複数の書き方ができるときがある
- ただし、書き方によって検索速度に大きな差がでる場合がある
検索速度が遅い…と感じたときは、４つの操作を確認し、ボトルネックを特定することが大事です。
まとめ
SELECTが何をしているのか混乱してきたら、表から表へ次々に変換していく、と考えれば、イメージしやすいです。
SELECTは、上達すれば、かなり複雑な条件でも検索できるように書けます。
ただその発想は、普通の手続き型プログラミングとは異なる独自の発想力が必要です。
まるでパズルのような複雑さ。うまくSELECT文を書けるようになるためには、いろいろな例題に触れることが大事です。
ポチップ
クラウドやマイクロサービスといった技術が普及していくなか、データベースを操作する技術は、ますます重要性が増してきています。
SELECTをマスターして、データベースエンジニアとしてのスキルアップを目指しましょう。
コメント