📘

SQL-サブクエリと複数テーブル

2023/06/19に公開

SQL

tech

SQL

こんにちは！わいわわです。
今回はSQLの学習まとめ記事です、progateさんで学んだことをまとめます。
サブクエリとASなど、
複数テーブルとJOINなどについてまとめていきます！

この記事のテーブルの前提

今回のテーブルは「サッカー」に関して3つのテーブルが用意されています。

countriesテーブル...id、name、rankカラム
例）
id1、日本、55
id2、アメリカ、24
それぞれの国のサッカー世界ランキングのようなイメージですね。

playersテーブル...id、name、goals、height、country-id、previous-team-idカラム
サッカー選手の名前、ゴール数、身長、そして2つのテーブルをidで紐づけています。
例）
id1、タクヤ、12、175、1、3

teamsテーブル...id、nameカラム
これは前に所属したチーム名がnameカラムにて格納されています。
例）
id1、板橋クラブ

以上3つのテーブルを使っていきます！

サブクエリと「AS」

基本的なSQLの記述に関しては以前、この記事で投稿しました！

あわせてみていただければと思います！

サブクエリ...クエリの中に他のクエリを入れることができ、その他のクエリのこと
2つ以上のクエリを1つにまとめることができるので、より複雑なデータを取得する際に使われます。
)で囲むことで、サブクエリを使用できます。
サブクエリの書き方は基本的に通常のクエリと同じですが、()内にセミコロン（;）は不要です！

「AS」...カラム名などに別名を定義すること
「カラム名 AS "名前"」で、カラム名に定義する名前を指定します。
SELECT以下で使用することでぱっと見で何を抽出しているかが分かります。

では、サブクエリとASを使った例文を見ていきましょう！

SELECT name AS "選手名", height AS "身長"
FROM players
WHERE height > (
  SELECT AVG(height)
  FROM players
)
;

上記の例文を説明していきます。
まず、全体の実行順序ですが、SQLのクエリでは
ーーーーーーーーーー
FROM
　↓
ON・JOIN
　↓
WHERE
　↓
GROUP BY
　↓
関数（SUMやAVGなど）
　↓
HAVING
　↓
SELECT・DISTINCT
　↓
ORDER BY
　↓
LIMIT
ーーーーーーーーーー
の順で実行されます。
これを頭に入れて読むと、理解しやすい気がします。

上記の例文ではまずFROM（playersテーブルから）→WHERE（サブクエリより大きな身長を持ってくる）と続きますが、
このWHEREでサブクエリが「（）」で記載されており、条件として
FROM（playersテーブルから）→SELECT（身長のAVGを検索）しています。
なのでこの最初のクエリのWHEREでは
「平均より身長が大きいカラムを持ってくる」というクエリ内容になりますね！

そして最初のクエリのSELECTで条件に合うname、heightをそれぞれASで、
"選手名"と"身長"という名前をつけている、イメージです。

そのためまとめるとこのクエリで抽出したのは
「平均身長より身長の高い選手の名前と身長」です！

複数テーブル

テーブル同士を紐づける記述を説明していきます。
テーブル同士を一緒に使うためには、
外部キーが設定されたカラムと主キーが設定されたカラムを用意しなければいけません。

先ほどのテーブルを再度確認しましょう。
countriesテーブル...id、name、rankカラム
playersテーブル...id、name、goals、height、country-id、previous-team-idカラム
この説明ではこの2つのテーブルを結合していきます。
countriesテーブルの「id」…主キー
playersテーブルのcountry-id…外部キー
playersテーブルのcountry-idにcountriesテーブルのidを結びつけることで、テーブルを複数扱うことができます。

表記方法は「JOIN」と「ON」です。
例文を記述します。

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
WHERE countries.name = "日本"
AND height >= 180
;

FROMに1つ目のテーブルを、JOINに2つ目のテーブルを、
そしてON以降に結合条件を記述することでテーブルを結合します。
ONは「.」をテーブルとカラムで挟むことで記述することができます。
今回の結合条件は
「playersテーブルのcountry-idカラムとcountriesテーブルのidカラムを結合する」
という意味ですね！

また、このように「.」でテーブルとカラムを指定できますが、
今回の例文ではWHEREにも使用されています。
これはplayersテーブルとcountriesテーブルどちらにも「name」というカラムがあり、
指定しなければどちらのテーブルのnameカラムか判別できないためです。
このような場合にこの記述はよく使用します！

LEFT JOIN

先ほど使用したJOINは外部キーのデータがNULLの時はデータを持ってきません。
排除したい場合はJOINで大丈夫ですが、実務ではNULLももってきたい場面があると思います。
そんな時に使用するのが「LEFT JOIN」です。
LEFT JOINを使うことで、FROMで指定したテーブルのレコードを全て取得し、
外部キーがNULLのレコードもNULLのまま実行結果に表示されます！

3つ以上のテーブルの結合

ちなみにテーブルの結合を3つ以上行いたい場合は

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
LEFT JOIN teams
ON players.previous_team_id = teams.id
;

このようにJOINもしくはLEFT JOINと、結合のONを繰り返すことで実現できます！
FROMは一回だけという点に注意しましょう！

所感

今回はSQLでサブクエリの記述やテーブルの結合について学習しました。
まだまだ基礎感が否めませんが、少しずつ行が増えていき、複雑になっていっていますね。
実務で活かせるよう、学習をさらに進めていきます！