[PostgreSQL] JOIN の種類

どれを使うか迷うことがあるので備忘録として。
検証で使用したpostgresのversionは12.7

前提

以下のようなテーブル定義とデータで検証

CREATE TABLE companies(
  id bigint primary key, 
  name varchar(50) NOT NULL,
  tel varchar(50)
);

CREATE TABLE employees(
  id bigint primary key, 
  company_id bigint,
  name varchar(50) NOT NULL,
  age integer,
  FOREIGN KEY (company_id) references companies(id)
);

INSERT INTO public.companies(id, name, tel)
VALUES 
    (1, '株式会社ほげ', '00-0000-0000'),
    (2, '株式会社ふが', '00-0000-1111'),
    (3, '株式会社ぴよ', '00-0000-2222');


INSERT INTO public.employees(id, company_id, name, age)
VALUES 
    (1, 1, 'テスト太郎', 25),
    (2, 1, 'テスト次郎', 20),
    (3, 2, 'テスト三郎', 30),
    (4, null, 'テスト五郎', 40);

(INNER) JOIN

内部結合。
結合条件(ON)に一致するレコードだけ表示。

SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
JOIN companies c ON c.id = e.company_id

結果

employee_id	employee_name	company_id	company_name
1	テスト太郎	1	株式会社ほげ
2	テスト次郎	1	株式会社ほげ
3	テスト三郎	2	株式会社ふが

LEFT (OUTER) JOIN

左外部結合。
左テーブル(FROMで指定したテーブル)をすべて表示して、
結合条件に一致しない右テーブル(JOINで指定したテーブル)はNULLとして結合される。

SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
LEFT JOIN companies c ON c.id = e.company_id

結果

employee_id	employee_name	company_id	company_name
1	テスト太郎	1	株式会社ほげ
2	テスト次郎	1	株式会社ほげ
3	テスト三郎	2	株式会社ふが
4	テスト五郎

RIGHT (OUTER) JOIN

右外部結合。
右テーブル(JOINで指定したテーブル)をすべて表示して、
結合条件に一致しない左テーブル(FROMで指定したテーブル)はNULLとして結合される。

SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM companies c
RIGHT JOIN employees e ON e.company_id = c.id

結果

LEFT JOINと同じ

FULL (OUTER) JOIN

完全外部結合。
LEFT JOIN と RIGHT JOIN の結果をあわせたもの。

SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
FULL JOIN companies c ON c.id = e.company_id

結果

employee_id	employee_name	company_id	company_name
1	テスト太郎	1	株式会社ほげ
2	テスト次郎	1	株式会社ほげ
3	テスト三郎	2	株式会社ふが
4	テスト五郎
		3	株式会社ぴよ

CROSS JOIN

両テーブルのすべてのレコードを取り出して、すべての組み合わせを表示する

SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
CROSS JOIN companies c
ORDER BY e.id

結果

employee_id	employee_name	company_id	company_name
1	テスト太郎	1	株式会社ほげ
1	テスト太郎	2	株式会社ふが
1	テスト太郎	3	株式会社ぴよ
2	テスト次郎	1	株式会社ほげ
2	テスト次郎	2	株式会社ふが
2	テスト次郎	3	株式会社ぴよ
...	...	...	...

参照

• PostgreSQL 12.4文書 7.2. テーブル式
• 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典