MySQL

Mysqlのテーブル結合について


ものすごい数のカラムが存在するデータベースから見たいものだけピックして見たい

この見出しのような状況が生まれるケースは例えば、nullの入ったカラムを特定して修正しなくてはならない時など。


方法 テーブル結合を利用する

テーブル結合は、簡単にうと、テーブル同士を合体させて、そこから検索をかけるというものです。

テーブル結合には、主に2種類に分けられます


inner join

異なるテーブル同士の指定カラムの中身が一致したものだけ結合してくれます。

inner joinのクエリ

mysql> select 結合したいカラム(複数選択をカンマで連結可能) from テーブル名1 inner join テーブル名2 on テーブル名1.カラム名1 = テーブル名2.カラム名2

このクエリの意味は、テーブル1のセレクトされたカラムから、テーブル1のカラム1とテーブル2のカラム2を対応させて合体させるという意味です。


outer join

異なるテーブル同士の指定カラムの中身が一致してないものまで全て結合してくれます。

結合されて合致していない箇所はNULLになります。

また、outer joinには更に2種類存在します。


  • LEFT OUTER JOIN

from句から見て、左側のテーブルを起点にしてテーブル同士を合体させます。

left outer joinのクエリ

mysql> select 結合したいカラム(複数選択をカンマで連結可能) 

from テーブル名1 left outer join テーブル名2 on テーブル名1.カラム名1 = テーブル名2.カラム名2

このクエリの意味は、テーブル1のカラムを第1レコードとして作り、テーブル1のカラム1とテーブル2のカラム2を対応させて合体させるという意味になります。


  • RIGHT OUTER JOIN

from句から見て、右側のテーブルを起点にしてテーブル同士を合体させます。

mysql> select 結合したいカラム(複数選択をカンマで連結可能) from テーブル名1 right outer join テーブル名2 on テーブル名1.カラム名1 = テーブル名2.カラム名2

このクエリの意味は、テーブル2のカラムを第1レコードとして作り、テーブル1のカラム1とテーブル2のカラム2を対応させて合体させるという意味になります。

以上がテーブル結合を行う方法になります。外部結合などをやると、NULLが返されるので、生合成を取れてないものが見つけやすいです。whereを使ってアプデをかけたりすれば、簡単に修正ができます。


おまけ

先ほどのクエリを実行すると、たくさんのカラムを持つテーブルを結合すると、

+---------+----------------------------------+----------------------------------+------------------------------------------+----------------------+---------------------+---------------------+------------------------------------------+---------------------------+------------+--------+------------------------------------------+------+---------------------+------------+-----------+--------------------------+----------------------------+----------------------+---------------+-----------------+-------------+--------+--------+----------------------+---------------+-----------+----------------------------+--------------------+---------------------------------------------------+----------------------------------------+-----------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+-------------+------------+---------+-------------+-------------------------------+---------------+-------------------------------+

| id | login | email | crypted_password | salt | created_at | updated_at | remember_token | remember_token_expires_at | deleted_at | status
+---------+----------------------------------+----------------------------------+------------------------------------------+----------------------+---------------------+---------------------+------------------------------------------+---------------------------+------------+--------+------------------------------------------+------+---------------------+------------+-----------+--------------------------+----------------------------+----------------------+---------------+-----------------+-------------+--------+--------+----------------------+---------------+-----------+----------------------------+--------------------+------------------

このように全く整合されない状態でドバッと出てくるので、

mysql> select 結合したいカラム(複数選択をカンマで連結可能) 

from テーブル名1 left outer join テーブル名2 on テーブル名1.カラム名1 = テーブル名2.カラム名2\G

このように末尾に\Gをしてあげると、

                                               id: *******

login: test.com
email: test.com
crypted_password: **************
salt: *****************
created_at: 2080-03-13 09:48:03
updated_at: 2080-04-15 16:36:34
remember_token: ***************
remember_token_expires_at: 2999-05-15 16:36:34

のように整合されて出力されるので、読みやすくなります。


おまけのおまけ

たくさんのカラムがある・・・という状況は、つまり似たような名前か、同じ名前の後にアンスコつけて機能を書き足してあるカラムとかあると思います。そんな時は、

mysql>show tables like '%検索に引っかかりそうなワード%'

ってやると似てるやつあらかた出してくれます。


おまけのおまけのおまけ

whereにin (フィールド,フィールド)をつけると、そのカラムの中身(フィールド)を指定したものをターゲティングできる。

mysql>select * from hogehoge_banners where id in (1,4);

とすると、

+----+-------+------+----------+----------+---------------------+---------------------+-----------+-----------------+

| id | image | link | position | category | created_at | updated_at | parent_id | parent_type |
+----+-------+------+----------+----------+---------------------+---------------------+-----------+-----------------+
| 1 | NULL | NULL | 7 | 4 | 2017-10-17 00:16:19 | 2017-10-17 00:16:19 | 1 | hogePrize |
| 4 | NULL | NULL | 8 | 4 | 2017-10-17 00:38:05 | 2017-10-17 00:38:05 | 4 | hogePrize |
+----+-------+------+----------+----------+---------------------+---------------------+-----------+-----------------+

となります。whereはすごい大事なので、他にもテクを覚えて行きたいです。