SQLで複数のテーブルのデータを利用する

2023年5月15日 16:59

サブクエリを活用する

　SQLではクエリの中にもう一つ別のクエリを入れ子で入れることができ、２つのクエリを1つにまとめることができる。入れ子で入るクエリを「サブクエリ」と呼ぶ。
　例えば、バスケットボールの選手情報を分析する場合を考える。選手情報をplayersテーブルに保存していて、選手名(name)、得点(points)がカラムとして含まれている場合、「トム」選手より得点が多い選手を検索する場合は以下のように記述する。

SELECT name
FROM players
WHERE points > (
 SELECT points
 FROM players
 WHERE name = "トム"
);

　クエリを()で囲むことでサブクエリとして利用できる。実行順は、サブクエリが実行された後に外のクエリが実行される。

nameカラムの値が「トム」のレコードを検索し
playersテーブルから
pointsカラムのデータを取得し
そのデータよりも大きいレコードを検索し
playersテーブルから
nameカラムの値を取得する

実行したSQLの結果に別名を定義する

　「AS」を使用し、「カラム名 AS "名前"」とすることで、指定したカラム名に別名を定義できる。

SELECT points AS "トムの得点数"
FROM players
WHERE name = "トム";

複数のテーブルのデータを活用する

テーブルを紐づける

　選手のデータを保存した「playersテーブル」に国の情報を保存した「countriesテーブル」を紐づける場合を考えます。「countriesテーブル」の主キー(idカラム)と紐づく外部キー(country_idカラム)を「playersテーブル」に追加します。外部キーで「countriesテーブル」の主キーを指定することでテーブル同士が紐づけられます。

テーブルを紐づけることのメリット

　管理のしやすさがメリットです。「playersテーブル」に「countriesテーブル」を紐づけると、「countriesテーブル」の内容に変更があっても、内容がそのまま「playersテーブル」に反映されます。
　一方、「playersテーブル」に国の情報を追加すると、国の情報に変更があった際、変更を複数のレコードで行わなくてはならい可能性があります。

テーブルを結合する

　「JOIN」を使用すると、紐づいたテーブル同士を結合し１つのテーブルとしてデータを取得することができる。「JOIN」に「ON」で条件を指定することもできる。「FROM」で指定したテーブルが基準のテーブルとなる。

SELECT *
FROM テーブル１
JOIN テーブル２
ON 結合条件；

　結合条件は以下のように指定する。

SELECT *
FROM テーブル１
JOIN テーブル２
ON テーブル１.カラム名(外部キー)　 = テーブル２.カラム名(主キー)；

　先ほどのバスケットボール選手の情報「playersテーブル」に、国の情報「countriesテーブル」を結合する場合は以下のようになる。

SELECT *
FROM players
JOIN countries
ON players.country_id　 = countries.id；

「playersテーブル」と「countriesテーブル」を「ON」の条件を指定し「JOIN」で結合する
全てのレコードを取得する

複数のテーブルでのカラムの指定

　複数のテーブルを利用してデータを取得する際、同じカラム名が存在する場合はどっちがどのテーブルのカラム名か分からなくなる。そのため、複数テーブルでのカラム指定には「テーブル名.カラム名」のように記述し、テーブルを指定した上でカラムを指定する必要がある。

#それぞれにnameカラムがある場合
SELECT name, name
FROM players
JOIN countries
ON players.country_id = countries.id

#テーブルを区別した場合
SELECT players.name, countries.name
FROM players
JOIN countries
ON players.country_id = countries.id

　このテーブルを指定した上でのカラムの指定は「WHERE」でも使用できる。

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
WHERE players.name = "トム";

NULLを含んだ場合のJOINの実行結果

　外部キーがNULLの場合は実行結果から除外され表示されない。NULLのレコードも取得し表示したい場合は「LEFT JOIN」を使う。
　「LEFT JOIN」を使用すると、「FROM」で指定したテーブルのレコードを全て取得し結合する。

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

　「JOIN」は一つのクエリで複数使用できる。「playersテーブル」に「countriesテーブル」と「teamsテーブル」を結合する場合は以下のように記述できる。

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

以上、複数テーブルを利用したデータの取得方法をまとめました。