「SQL」JOINとEXISTSの備忘録。

SQL の JOIN と EXISTS の備忘録。

JOIN

テーブル結合に利用する。INNER JOIN と LEFT(RIGHT) OUTER JOIN の２種類がある。

以下のusers、role、department テーブルを想定します。

test_db=# select * from users ;
 id |  name   | role_id | department_id
----+---------+---------+---------------
  1 | name_a  |       1 |           100
  2 | name_b  |       2 |           200
  3 | name_c  |       3 |           100
  4 | name_aa |       1 |           100

test_db=# select * from role;
 id |  name
----+--------
  1 | role_a
  2 | role_b
  3 | role_c
  4 | role_d

test_db=# select * from department ;
 id  | name
-----+-------
 100 | d_100
 200 | d_200
 300 | d_300

1.ロールID(role_id) を結合条件として内部結合する

test_db=# select u.id as user_id, u.name as user_name, r.name as role_name from users u 
inner join role r on u.role_id = r.id;
 user_id | user_name | role_name
---------+-----------+-----------
       1 | name_a    | role_a
       2 | name_b    | role_b
       3 | name_c    | role_c
       4 | name_aa   | role_a

2. ロールID(role_id) を結合条件として(右)外部結合する

JOINで指定する結合する側のテーブル(以下だとrole)が残ります。 roleテーブルのid=4であるデータはuserテーブルに存在しませんが、右外部結合だとid=4のroleテーブルの情報が残っています。

test_db=# select u.id, u.name, r.name from users u 
right outer join role r on u.role_id = r.id;
 id |  name   |  name
----+---------+--------
  1 | name_a  | role_a
  2 | name_b  | role_b
  3 | name_c  | role_c
  4 | name_aa | role_a
    |         | role_d

3. ３つのテーブルを結合する

JOINは並べて書くことができるので以下のようにして３つのテーブルを結合することも可能です。 department というテーブルを追加しています。

test_db=# select u.id, u.name, r.name as role_name, d.name as department_name from users u 
inner join role r on u.role_id = r.id 
inner join department d on u.department_id = d.id;
 id |  name   | role_name | department_name
----+---------+-----------+-----------------
  1 | name_a  | role_a    | d_100
  2 | name_b  | role_b    | d_200
  3 | name_c  | role_c    | d_100
  4 | name_aa | role_a    | d_100

EXISTS

1. ユーザ(users)が一人でも所属する部署(department)を取得する

test_db=# select * from department where exists (select * from users where users.department_id = department.id);
 id  | name
-----+-------
 100 | d_100
 200 | d_200

2. ユーザ(users)が一人も所属しない部署(department)を取得する

test_db=# select * from department where not exists (select * from users where users.department_id = department.id);
 id  | name
-----+-------
 300 | d_300

SQLはそんなに書く場面も最近はないし、いざ書こうと思っても苦手もありハードルが高いのでついついコード側でなんとかしてしまうことが多いですが、せめて人並みには使えるようになりたいと思う今日このごろです。

以上です。