SQL FULL OUTER JOIN

概要: このチュートリアルでは、SQL FULL OUTER JOIN句を使用して複数のテーブルからデータをクエリする方法を学びます。

SQL FULL OUTER JOIN句の紹介

理論的には、full outer joinはleft joinとright joinの組み合わせです。full outer joinは、結合されたテーブルに一致する行があるかどうかに関係なく、結合されたテーブルのすべての行を含みます。

結合されたテーブルの行が一致しない場合、full outer joinの結果セットには、一致する行がないテーブルのすべての列にNULL値が含まれます。一致する行については、結合されたテーブルから列が設定された単一行が結果セットに含まれます。

次のステートメントは、2つのテーブルのfull outer joinの構文を示しています。

SELECT column_list
FROM A
FULL OUTER JOIN B ON B.n = A.n;Code language: SQL (Structured Query Language) (sql)

OUTERキーワードはオプションであることに注意してください。

次のベン図は、2つのテーブルのfull outer joinを示しています。

SQL FULL OUTER JOINの例

FULL OUTER JOIN句を使用して、それがどのように機能するかを見てみましょう。

まず、デモンストレーションのために、2つの新しいテーブル：basketsとfruitsを作成します。各バスケットには0個以上の果物が格納され、各果物は0個または1個のバスケットに格納できます。

CREATE TABLE fruits (
	fruit_id INTEGER PRIMARY KEY,
	fruit_name VARCHAR (255) NOT NULL,
	basket_id INTEGER
);Code language: SQL (Structured Query Language) (sql)

CREATE TABLE baskets (
	basket_id INTEGER PRIMARY KEY,
	basket_name VARCHAR (255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

次に、basketsテーブルとfruitsテーブルにいくつかのサンプルデータを挿入します。

INSERT INTO baskets (basket_id, basket_name)
VALUES
	(1, 'A'),
	(2, 'B'),
	(3, 'C');
Code language: SQL (Structured Query Language) (sql)

INSERT INTO fruits (
	fruit_id,
	fruit_name,
	basket_id
)
VALUES
	(1, 'Apple', 1),
	(2, 'Orange', 1),
	(3, 'Banana', 2),
	(4, 'Strawberry', NULL);Code language: SQL (Structured Query Language) (sql)

3番目に、次のクエリは、バスケットに入っている各果物と果物が入っている各バスケットを返しますが、バスケットに入っていない各果物と果物が入っていない各バスケットも返します。

SELECT
	basket_name,
	fruit_name
FROM
	fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id;Code language: SQL (Structured Query Language) (sql)

 basket_name | fruit_name
-------------+------------
 A           | Apple
 A           | Orange
 B           | Banana
 (null)      | Strawberry
 C           | (null)Code language: SQL (Structured Query Language) (sql)

ご覧のとおり、バスケットCには果物がなく、Strawberryはどのバスケットにも入っていません。

FULL OUTER JOIN句を使用するステートメントにWHERE句を追加して、より具体的な情報を取得できます。

たとえば、果物を格納していない空のバスケットを見つけるには、次のステートメントを使用します。

SELECT
	basket_name,
	fruit_name
FROM
	fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
	fruit_name IS NULL;Code language: SQL (Structured Query Language) (sql)

 basket_name | fruit_name
-------------+------------
 C           | (null)
(1 row)Code language: SQL (Structured Query Language) (sql)

同様に、どの果物がどのバスケットにも入っていないかを確認する場合は、次のステートメントを使用します。

SELECT
	basket_name,
	fruit_name
FROM
	fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
	basket_name IS NULL;Code language: SQL (Structured Query Language) (sql)

 basket_name | fruit_name
-------------+------------
(null)       | Strawberry
(1 row)Code language: SQL (Structured Query Language) (sql)

このチュートリアルでは、SQL FULL OUTER JOIN句を使用して複数のテーブルからデータをクエリする方法を説明しました。