sql_結合とサブクエリ

07結合とサブクエリ

JOIN（結合）

JOINは、複数のテーブルから関連するデータを結合して一つの結果セットを作成するために使用します。JOINを使用することによって、異なるテーブルに分散して格納されているデータを関連付けて、必要な情報を効率的に取り出すことができます。

JOINの基本的な概念は、少なくとも一つの共通の属性（カラム）を持つ二つ以上のテーブル間でのデータの関連付けです。この共通の属性を基にして、指定された条件に一致する行同士が結合されます。

JOINにはいくつかの方法があります。ここまで使用してきた学生（students）テーブル、学生の成績（scores）テーブルを使って、紹介していきます。

まずは以下SQLを実行し、説明に必要なデータを準備してください。

①学生を追加

insert into school.students
   (name, age, class)
values
   ('森田五郎', 12, 3);

②成績を追加

insert into school.scores
   (student_id, subject, score)
values
   (2, '科学', 65);

Inner Join（内部結合）

両方のテーブルに存在する行のみを結果として返します。つまり、結合条件に合致する行だけが取り出されます。

今回のテーブルの場合、「すべての学生と、それに対応する成績データを表示させたいが、成績が記録されている学生のみをリストアップしたい。」のようなユースケースの時にInner Joinを使用します。

select 
   students.name, 
   students.class, 
   scores.subject, 
   scores.score
from 
   students
inner join scores 
   on students.id = scores.student_id;

Joinでは「テーブル名.カラム名」をセットで書く必要があり、SQL自体が比較的長くなってしまうことがあります。そういった場合には、「テーブル名 as エイリアス（略称）」テーブル名にエイリアス（略称）を設定することが可能です。

※as を省略することもできますが、分かりやすいように明記しています。

また、inner join は joinに変更可能です。

select 
   s.name, 
   s.class, 
   sc.subject, 
   sc.score
from 
   students as s
join scores as sc 
   on s.id = sc.student_id;

今回の取得結果は以下のようになります。

「結合条件に当てはまるものだけを取得する」のがInner Joinなので、取得対象としてはscoresテーブルに登録されているレコードがある生徒のみということになります。

※Inner Join時のイメージ（背景が黄色になっているレコードが取得対象）

Left Outer Join（左外部結合）

Left Outer Joinは、左側のテーブルの全ての行と、右側のテーブルの条件に合致する行が結果に含まれます。右側のテーブルに合致する行がない場合は、その部分にはNULLが入ります。

今回のテーブルの場合、「全ての学生のリストを表示し、成績データがあればその情報も表示したい。成績データがない学生については、その部分をNULLで表示したい」というようなユースケースの時に使用できます。

select 
   s.name, 
   s.class, 
   sc.subject, 
   sc.score
from 
   school.students s
left outer join school.scores sc 
   on s.id = sc.student_id;

※left outer join は left joinに省略可能

今回の取得結果は以下のようになります。

「左側のテーブルのすべての行」を出力するので、成績のレコードを持たない学生も全員出力されます。

※Left Outer Join時のイメージ（背景が黄色になっているレコードが取得対象）

Right Outer Join（右外部結合）

Right Outer Joinは、右側のテーブルの全ての行と、左側のテーブルの条件に合致する行が結果に含まれます。左側のテーブルに合致する行がない場合は、その部分にはNULLが入ります。

今回のテーブル構造の場合、成績（scores）テーブルのstudent_idに外部キー制約を設定しています（※）。外部キー制約とは、「親テーブルにないデータを子テーブルが持つことが無いようにする制約」です。今回の場合だと「studentsテーブルに存在しないidを、socresテーブルでstudent_idとして持つことはできない」ということになります。

（※）テーブル作成時に設定をしました。

以上の事から、今回のデータを使ってRight Outer Joinの動作確認を行うことは難しいですが、例えの値を用いて説明すると、「すべての成績記録を表示し、対応する学生情報も表示したい。ただし、成績が登録されているが学生情報が見つからない場合、その学生情報はNULLで表示する。」といったユースケースの時に使用するということになります。

※Right Outer Join時のイメージ（背景が黄色になっているレコードが取得対象）

※分かりやすくするため、今回存在しないデータを追加しています。

Full Outer Join（全外部結合）

Full Outer Join（全外部結合）では、二つのテーブル間で関連する全てのレコードを結合し、一方のテーブルには存在するが他方には存在しないレコードも含む結果を返します。この結合により、両方のテーブルに共通するレコードだけでなく、どちらか一方のテーブルにのみ存在するレコードも結果に含まれます。「Left Outer JoinとRight Outer Joinを組み合わせたもの」だと認識していただければ問題ないです。

Union / Union All（統合結合）

Union演算子を使用すると、2つ以上のSELECT文の結果を組み合わせて1つの結果セットを作成することができます。

ここでは、先生（teachers）テーブルを作成し、生徒（students）テーブルと組み合わせて同じクラスの先生と生徒を抽出してみましょう。

まずは以下SQLを実行し、説明に必要なデータを準備してください。

①teachersテーブルを作成

create table teachers(
   id INT(11) AUTO_INCREMENT NOT NULL,
   name VARCHAR(30) NOT NULL,
   class INT(3) NOT NULL,
   PRIMARY KEY (id)
);

②teachersテーブルに先生を追加

insert into school.teachers
   (name, class)
values
   ('高木太郎', 1),
   ('佐藤一郎', 4),
   ('鈴木次郎', 2),
   ('森田五郎', 3);

クラス3に属する生徒と先生はそれぞれ以下のようになっています。

生徒

先生

では、これらのデータを1つにまとめて取得しましょう。

以下を実行してください。

select name from students where class = 3
union
select name from teachers where class = 3;

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

Unionを使用することによって、複数のSELECT文の結果を結合しますが、重複する行は一つにまとめられます。ゆえに、同姓同名だった「森田五郎（生徒）」と「森田五郎（先生）」は重複する行として1つにまとめられました。

重複する行を1つにまとめたくない場合は、Union Allを使用します。

select name from students where class = 3
union all
select name from teachers where class = 3;

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

Union Allを使用することで、名前の重複が排除されず、生徒と先生どちらの「森田五郎」さんも抽出することができました。

サブクエリ

サブクエリ（副問い合わせ）は、SQL文の中で別のSQL文を使用する強力な方法です。サブクエリを使うことで、複雑なデータ操作をシンプルに行うことができます。

サブクエリの使用例①（where句での使用）

学生の平均年齢を計算し、その

select 
   *
from 
   students
where age > (
   select avg(age) from students
);

以下のように、全学生の平均年齢以上の学生を取得することが可能です。

サブクエリの使用例②（select句での使用）

各学生の名前と、その学生が取得した最高点を一緒に表示します。

select
   id,
   name,
   (select max(score) from scores where scores.student_id = students.id) as highest_score
from 
   students;

以下のように、各学生の最高点とともにデータを取得します。

サブクエリの使用例③（from句での使用）

各クラスの平均年齢とそのクラスに所属する学生の人数を表示します。

select 
   avg_ages.class, 
   avg_ages.avg_age, 
   count(students.id) as student_count
from (
   select 
      class, 
      avg(age) as avg_age
   from 
      students
   group by class
) as avg_ages
join students 
on avg_ages.class = students.class
group by avg_ages.class, avg_ages.avg_age;

以下のように、各クラスの平均年齢と人数を取得できます。

※サブクエリを使用する際の注意点

パフォーマンスサブクエリは強力ですが、パフォーマンスに影響を与える場合があります。特に大量のデータに対して多くのサブクエリを使用すると、クエリの実行時間が長くなる可能性があります。必要に応じて、適切なインデックスを設定するか、結合（JOIN）を使用することを検討してください。
可読性サブクエリを多用すると、クエリが複雑になり、可読性が低下することがあります。クエリの構造を明確にし、コメントを追加して可読性を保つように心がけましょう。