複数のテーブルを結合する
RDB はリレーショナルなデータベースであり、複数のテーブルを結びつけることができる。
テーブルの結合の仕方には以下の 3 つの方法がある。
- 和集合 (UNION 結合)
- 内部結合 (INNER JOIN)
- 外部結合 (OUTER JOIN)
- 交差結合 (CROSS JOIN)
ここでは、和集合、内部結合、外部結合について記載する。
なお、今回は Protocol 、Protocol2 、Application という二つのテーブルを用いる。
mysql> SELECT * FROM Protocol;
+------+------+--------+
| Id | Name | Layer4 |
+------+------+--------+
| 0 | HTTP | TCP |
| 1 | SSH | TCP |
| 2 | FTP | TCP |
| 3 | NFS | TCP |
| 4 | DHCP | UDP |
| 5 | SIP | UDP |
| 6 | DNS | UDP |
| 7 | SNMP | UDP |
+------+------+--------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM Protocol2;
+------+--------+--------+
| Id | Name | Layer4 |
+------+--------+--------+
| 8 | SMTP | TCP |
| 9 | Telnet | TCP |
| 10 | BGP | TCP |
+------+--------+--------+
3 rows in set (0.02 sec)
mysql> SELECT * FROM Application;
+------------+----------+
| Name | Protocol |
+------------+----------+
| WebBrowser | HTTP |
| EFS | NFS |
| Bind | DNS |
| dhclient | DHCP |
+------------+----------+
4 rows in set (0.04 sec) 和集合 (UNION 結合)
その名の通り和集合となり、テーブルとテーブルがそのまま結合される。結合するテーブルの列数は同じである必要がある。
また、対応する位置の列の型も同一である必要がある。
mysql>
mysql> SELECT * FROM Protocol UNION ALL SELECT * FROM Protocol2;
+------+--------+--------+
| Id | Name | Layer4 |
+------+--------+--------+
| 0 | HTTP | TCP |
| 1 | SSH | TCP |
| 2 | FTP | TCP |
| 3 | NFS | TCP |
| 4 | DHCP | UDP |
| 5 | SIP | UDP |
| 6 | DNS | UDP |
| 7 | SNMP | UDP |
| 8 | SMTP | TCP |
| 9 | Telnet | TCP |
| 10 | BGP | TCP |
+------+--------+--------+
11 rows in set (0.00 sec) 内部結合 (INNER JOIN)
内部結合では全てのレコードが結合されるわけではなく、基準となる列の値が共通するレコードのみを取り出す形となる。
mysql> SELECT * FROM Protocol INNER JOIN Application ON Protocol.Name = Application.Protocol;
+------+------+--------+------------+----------+
| Id | Name | Layer4 | Name | Protocol |
+------+------+--------+------------+----------+
| 0 | HTTP | TCP | WebBrowser | HTTP |
| 3 | NFS | TCP | EFS | NFS |
| 4 | DHCP | UDP | dhclient | DHCP |
| 6 | DNS | UDP | Bind | DNS |
+------+------+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM Protocol, Application WHERE Protocol.Name = Application.Protocol;
+------+------+--------+------------+----------+
| Id | Name | Layer4 | Name | Protocol |
+------+------+--------+------------+----------+
| 0 | HTTP | TCP | WebBrowser | HTTP |
| 3 | NFS | TCP | EFS | NFS |
| 4 | DHCP | UDP | dhclient | DHCP |
| 6 | DNS | UDP | Bind | DNS |
+------+------+--------+------------+----------+
4 rows in set (0.01 sec) 外部結合 (OUTER JOIN)
外部結合も内部結合と同様に、列が追加される結合となる。主たるテーブルと副になるテーブルを見比べ、共通するレコードは内部結合と同じように合体される。
主テーブルにあって副テーブルにないものは NULL として表記され、主たるテーブルのレコードが減ることはない。
外部結合には、左外部結合 (LEFT OUTER JOIN) と右外部結合 (RIGHT OUTER JOIN) があり、主テーブルに列を結合していく形で行われる。
左外部結合 (LEFT OUTER JOIN)
mysql> SELECT * FROM Protocol LEFT JOIN Application ON Protocol.Name = Application.Protocol;
+------+------+--------+------------+----------+
| Id | Name | Layer4 | Name | Protocol |
+------+------+--------+------------+----------+
| 0 | HTTP | TCP | WebBrowser | HTTP |
| 3 | NFS | TCP | EFS | NFS |
| 6 | DNS | UDP | Bind | DNS |
| 4 | DHCP | UDP | dhclient | DHCP |
| 1 | SSH | TCP | NULL | NULL |
| 2 | FTP | TCP | NULL | NULL |
| 5 | SIP | UDP | NULL | NULL |
| 7 | SNMP | UDP | NULL | NULL |
+------+------+--------+------------+----------+
8 rows in set (0.04 sec) 右外部結合 (RIGHT OUTER JOIN)
mysql> SELECT * FROM Protocol RIGHT JOIN Application ON Protocol.Name = Application.Protocol;
+------+------+--------+------------+----------+
| Id | Name | Layer4 | Name | Protocol |
+------+------+--------+------------+----------+
| 0 | HTTP | TCP | WebBrowser | HTTP |
| 3 | NFS | TCP | EFS | NFS |
| 4 | DHCP | UDP | dhclient | DHCP |
| 6 | DNS | UDP | Bind | DNS |
+------+------+--------+------------+----------+
4 rows in set (0.00 sec)