.. meta::
   :keywords: web2py, framework, DAL, データベース抽象化レイヤ, join, テーブル結合



.. _join:

テーブル結合（join）
====================

web2py では明示的なテーブル結合の設定として次のものがあります。

	* Setクラスのコンストラクタでの内部結合（inner join）
	* Setクラスのselectメソッドでの内部結合（inner join）
	* Setクラスのselectメソッドでの左外部結合（left outer join）

これらについて順番に説明していきます。

テーブル結合（join）の基本
--------------------------

1. 内部結合（inner join）
^^^^^^^^^^^^^^^^^^^^^^^^^

内部結合は、２つのテーブルの一致する列の行だけを組み合わせて一つの行にします。一致しない行は除外されます。

	.. image:: ../../images/web2py_dal/web2py_dal_017r.JPG
	   :width: 80%
	   :scale: 100%


参考： `Inner Joins <http://web2py.com/book/default/chapter/06#Inner-joins>`_ ｜ `内部結合(Inner Joins) <http://www.web2py.com/books/default/chapter/33/06#内部結合(Inner-Joins)>`_


1.1. Setインスタンス生成時での内部結合定義
""""""""""""""""""""""""""""""""""""""""""

Setインスタンス生成時（ :ref:`class_set_instantiate` ）に、パラメータのQueryインスタンスを使用して内部結合の設定ができます。 ::

	>>> print db(db.person.id==db.dog.owner_id).select()
	person.id,person.name,dog.id,dog.owner_id,dog.name
	2,Socepopa,1,2,Comoceta
	3,Cosotama,2,3,Soducomo
	3,Cosotama,3,3,Cosasamo

パラメータとして渡す :ref:`class_query` インスタンスに、内部結合での条件式を記述します。

サンプルでのSQL文は次のようになります。 ::

	>>> print db(db.person.id==db.dog.owner_id)._select()
	SELECT  person.id, person.name, dog.id, dog.owner_id, dog.name FROM person, dog
	WHERE (person.id = dog.owner_id);


.. _select_inner_join:

1.2. selectメソッドでの内部結合定義
"""""""""""""""""""""""""""""""""""

Setクラスの :meth:`~dal.Set.select` メソッドでも内部結合の設定が可能です。 ::

	>>> print db().select(db.person.ALL, db.dog.ALL, join=db.dog.on(db.person.id==db.dog.owner_id))
	person.id,person.name,dog.id,dog.owner_id,dog.name
	2,Socepopa,1,2,Comoceta
	3,Cosotama,2,3,Soducomo
	3,Cosotama,3,3,Cosasamo

selectメソッドの joinオプションを使います。また併せて :meth:`~dal.Table.on` メソッドも使用します。

サンプルでのSQL文は次のようになります。 ::

	>>> print db()._select(db.person.ALL, db.dog.ALL, join=db.dog.on(db.person.id==db.dog.owner_id))
	SELECT  person.id, person.name, dog.id, dog.owner_id, dog.name FROM person JOIN dog
	ON (person.id = dog.owner_id);

SetコンストラクタはWHERE文で内部結合の定義しています。しかしこちらではJOIN句を使っています。


.. _select_left_join:

2. 左外部結合（left outer join）
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

左外部結合では、左側テーブルは（一致しなくても）すべての行を組み合わせます。一致しないテーブルのデータはNULL値になります。


	.. image:: ../../images/web2py_dal/web2py_dal_018r.JPG
	   :width: 80%
	   :scale: 100%

この結合の設定では、 :meth:`~dal.Set.select` メソッドの left オプションを使用します。 ::

	>>> print db().select(db.person.ALL, db.dog.ALL,left=db.dog.on(db.person.id==db.dog.owner_id))
	person.id,person.name,dog.id,dog.owner_id,dog.name
	1,Pacedada,<NULL>,<NULL>,<NULL>
	2,Socepopa,1,2,Comoceta
	3,Cosotama,2,3,Soducomo
	3,Cosotama,3,3,Cosasamo

ここで左側テーブルというのは、left オプションで指定しない方のテーブル（結合したテーブルではない方）を指しています。

サンプルでのSQL文は次のようになります。 ::

	>>> print db()._select(db.person.ALL, db.dog.ALL,left=db.dog.on(db.person.id==db.dog.owner_id))
	SELECT  person.id, person.name, dog.id, dog.owner_id, dog.name FROM person LEFT JOIN dog
	ON (person.id = dog.owner_id);

参考： `Left Outer Join <http://web2py.com/book/default/chapter/06#Left-outer-join>`_ ｜ `左外部結合(Left Outer Join) <http://www.web2py.com/books/default/chapter/33/06#左外部結合(Left-Outer-Join)>`_


.. _join_multiple1:

複雑なテーブル結合（join）１
----------------------------

少し複雑なテーブル結合について、考えてみたいと思います。３つのテーブルを使ったテーブル結合です。

1. 内部結合（inner join）
^^^^^^^^^^^^^^^^^^^^^^^^^

次のように複数の内部結合がある場合、３つのテーブルの一致する列の行だけを組み合わせて一つの行にします。一致しない行はすべて除外されます。

	.. image:: ../../images/web2py_dal/web2py_dal_029r.PNG
	   :scale: 100%

1.2. Setインスタンス生成時での内部結合定義
""""""""""""""""""""""""""""""""""""""""""

Queryインスタンスを使用して複数の内部結合を記述するには、 :ref:`class_query_logical_operator` を使用します。 ::

	>>> print db((db.person.id==db.dog.owner_id)&(db.dog.hospital==db.animal_hospital.id)).select()
	person.id,person.name,dog.id,dog.owner_id,dog.hospital,dog.name,animal_hospital.id,animal_hospital.name
	2,Socepopa,1,2,1,Comoceta,1,North
	2,Socepopa,2,2,1,Soducomo,1,North

論理演算子を使わず、 :ref:`class_set_call` で記述することも可能です。 ::

	>>> print db(db.person.id==db.dog.owner_id)(db.dog.hospital==db.animal_hospital.id).select()
	person.id,person.name,dog.id,dog.owner_id,dog.hospital,dog.name,animal_hospital.id,animal_hospital.name
	2,Socepopa,1,2,1,Comoceta,1,North
	2,Socepopa,2,2,1,Soducomo,1,North


1.2. selectメソッドでの内部結合定義
"""""""""""""""""""""""""""""""""""

:meth:`~Set.select` メソッドで複数の内部結合を設定するには、joinに複数の条件を＆演算子で結合して指定します。 ::

	>>> print db().select(db.person.ALL, db.dog.ALL, db.animal_hospital.ALL,
	... join=db.dog.on((db.person.id==db.dog.owner_id)&(db.animal_hospital.id==db.dog.hospital)))
	person.id,person.name,dog.id,dog.owner_id,dog.hospital,dog.name,animal_hospital.id,animal_hospital.name
	2,Socepopa,1,2,1,Comoceta,1,North
	2,Socepopa,2,2,1,Soducomo,1,North

この他 join パラメータにリストもしくはタプルで複数指定することで、実行することも可能です。 ::

	>>> print db().select(db.person.ALL, db.dog.ALL, db.animal_hospital.ALL,
	... join=[db.person.on(db.person.id==db.dog.owner_id),
	... db.animal_hospital.on(db.animal_hospital.id==db.dog.hospital)])
	person.id,person.name,dog.id,dog.owner_id,dog.hospital,dog.name,animal_hospital.id,animal_hospital.name
	2,Socepopa,1,2,1,Comoceta,1,North
	2,Socepopa,2,2,1,Soducomo,1,North


2. 左外部結合（left outer join）
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

左外部結合で複数の結合が存在した場合でも設定可能です。左側テーブル（db.person）はすべての行を組み合わせます。一致しないテーブルのデータはNULL値になります。

	.. image:: ../../images/web2py_dal/web2py_dal_030r.PNG
	   :scale: 100%

left パラメータに、リストもしくはタプルで複数指定します。 ::

	>>> print db().select(db.person.ALL, db.dog.ALL, db.animal_hospital.ALL,
	... left=[db.dog.on(db.person.id==db.dog.owner_id),
	... db.animal_hospital.on(db.animal_hospital.id==db.dog.hospital)])
	person.id,person.name,dog.id,dog.owner_id,dog.hospital,dog.name,animal_hospital.id,animal_hospital.name
	1,Pacedada,<NULL>,<NULL>,<NULL>,<NULL>,<NULL>,<NULL>
	2,Socepopa,1,2,1,Comoceta,1,North
	2,Socepopa,2,2,1,Soducomo,1,North
	3,Cosotama,3,3,<NULL>,Cosasamo,<NULL>,<NULL>


.. _join_multiple2:

複雑なテーブル結合（join）２
----------------------------

さらに複雑なテーブル結合を考えてみます。一方のテーブルから同じテーブルに複数の結合を行なってみます。

1. 内部結合（inner join）
^^^^^^^^^^^^^^^^^^^^^^^^^

次のように同じテーブルに対して複数の内部結合がある場合、一致する列の行だけを組み合わせて一つの行にします。一致しない行はすべて除外されます。

	.. image:: ../../images/web2py_dal/web2py_dal_031r.PNG
	   :scale: 100%

このようなタイプの結合では、selectメソッドでの内部結合定義を利用します。また同じテーブル同士で複数の結合があるので、別名を付ける必要があります。 ::

	>>> print db().select(db.cat.ALL, db.owner1.ALL, db.owner2.ALL,
	... join=[db.person.with_alias('owner1').on(db.owner1.id==db.cat.owner1),
	... db.person.with_alias('owner2').on(db.owner2.id==db.cat.owner2)])
	cat.id,cat.name,cat.owner1,cat.owner2,owner1.id,owner1.name,owner2.id,owner2.name
	1,Comoceta,2,3,2,Socepopa,3,Cosotama
	2,Suducomo,2,1,2,Socepopa,1,Pacedada

つまりcatテーブルのowner1フィールドの結合に対しては :meth:`~dal.Table.with_alias` メソッドで owner1 という別名を、
owner2フィールドに対しては別名として owner2 を設定しています。

サンプルでのSQL文は次のようになります。 ::

	>>> print db()._select(db.cat.ALL, db.owner1.ALL, db.owner2.ALL,
	... join=[db.person.with_alias('owner1').on(db.owner1.id==db.cat.owner1),
	... db.person.with_alias('owner2').on(db.owner2.id==db.cat.owner2)])
	SELECT  cat.id, cat.name, cat.owner1, cat.owner2, owner1.id, owner1.name, owner2.id, owner2.name
	FROM cat JOIN person AS owner1 ON (owner1.id = cat.owner1) JOIN person AS owner2 ON (owner2.id = cat.owner2);

2. 左外部結合（left outer join）
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

同じテーブルに対して複数の内部結合がある場合も、左側テーブル（db.cat）はすべての行を組み合わせます。一致しないテーブルのデータはNULL値になります。

	.. image:: ../../images/web2py_dal/web2py_dal_032r.PNG
	   :scale: 100%

左外部結合でも内部結合と同様に、別名で設定する必要があります。つまり、join パラメータの代わりに left を使用すれば、そのまま実行できます。 ::

	>>> print db().select(db.cat.ALL, db.owner1.ALL, db.owner2.ALL,
	... left=[db.person.with_alias('owner1').on(db.owner1.id==db.cat.owner1),
	... db.person.with_alias('owner2').on(db.owner2.id==db.cat.owner2)])
	cat.id,cat.name,cat.owner1,cat.owner2,owner1.id,owner1.name,owner2.id,owner2.name
	1,Comoceta,2,3,2,Socepopa,3,Cosotama
	2,Suducomo,2,1,2,Socepopa,1,Pacedada
	3,Cosasamo,3,<NULL>,3,Cosotama,<NULL>,<NULL>