MySQLでFULL OUTER JOIN(と同じこと)をする

MySQLではFULL OUTER JOINは使えないので、2つのテーブルでどちらか一方にデータが存在する場合の結合はUNIONを用いる必要がある。

例

ENGLISHとMATHをstudent_idで結合し、それぞれのscoreの平均値を表示したい。
（ENGLISHのscoreしか無いstudent、MATHのscoreしか無いstudent、両方のscoreがあるstudentがいるが、どちらか一方でもscoreがあれば表示する。）

ENGLISHテーブル

student_id	score	date
A1	100	2023-04-01
A2	50	2023-04-01
A5	70	2023-04-01
A1	80	2023-09-01
A2	30	2023-09-01
A5	40	2023-09-01

MATHテーブル

student_id	score	date
A1	80	2023-04-01
A3	80	2023-04-01
A4	80	2023-04-01
A1	50	2023-09-01
A3	60	2023-09-01
A4	70	2023-09-01

SELECT E.student_id,AVG(E.score),AVG(M.score)  
FROM ENGLISH AS E  
	LEFT OUTER JOIN  
     MATH AS M USING(student_id)  
GROUP BY student_id  

UNION  

SELECT M.student_id,AVG(E.score),AVG(M.score)  
FROM MATH AS M  
	LEFT OUTER JOIN  
     ENGLISH AS E USING(student_id)  
GROUP BY student_id 

ORDER BY student_id

結果

student_id	AVG(E.score)	AVG(M.score)
A1	90	65
A2	40	NULL
A3	NULL	70
A4	NULL	75
A5	55	NULL

ENGLISHテーブルに存在するstudent_idとMATHテーブルに存在するstudent_idでそれぞれ集計してそれをUNIONする。

※UNIONの後のSELECT文は以下でも可

SELECT M.student_id,AVG(E.score),AVG(M.score)  
FROM ENGLISH AS E  
	RIGHT OUTER JOIN  
     MATH AS M USING(student_id)  
GROUP BY student_id