2.2. テーブル結合（join）¶
web2py では明示的なテーブル結合の設定として次のものがあります。
- Setクラスのコンストラクタでの内部結合（inner join）
- Setクラスのselectメソッドでの内部結合（inner join）
- Setクラスのselectメソッドでの左外部結合（left outer join）
これらについて順番に説明していきます。
2.2.1. テーブル結合（join）の基本¶
1. 内部結合（inner join）¶
内部結合は、２つのテーブルの一致する列の行だけを組み合わせて一つの行にします。一致しない行は除外されます。
参考： Inner Joins ｜ 内部結合(Inner Joins)
1.1. Setインスタンス生成時での内部結合定義¶
Setインスタンス生成時（ インスタンス化 ）に、パラメータの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
パラメータとして渡す 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);
1.2. selectメソッドでの内部結合定義¶
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オプションを使います。また併せて 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句を使っています。
2. 左外部結合（left outer join）¶
左外部結合では、左側テーブルは（一致しなくても）すべての行を組み合わせます。一致しないテーブルのデータはNULL値になります。
この結合の設定では、 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);
2.2.2. 複雑なテーブル結合（join）１¶
少し複雑なテーブル結合について、考えてみたいと思います。３つのテーブルを使ったテーブル結合です。
1. 内部結合（inner join）¶
次のように複数の内部結合がある場合、３つのテーブルの一致する列の行だけを組み合わせて一つの行にします。一致しない行はすべて除外されます。
1.2. Setインスタンス生成時での内部結合定義¶
Queryインスタンスを使用して複数の内部結合を記述するには、 論理演算子 を使用します。
>>> 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
論理演算子を使わず、 関数へのエミュレーション で記述することも可能です。
>>> 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メソッドでの内部結合定義¶
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値になります。
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>
2.2.3. 複雑なテーブル結合（join）２¶
さらに複雑なテーブル結合を考えてみます。一方のテーブルから同じテーブルに複数の結合を行なってみます。
1. 内部結合（inner join）¶
次のように同じテーブルに対して複数の内部結合がある場合、一致する列の行だけを組み合わせて一つの行にします。一致しない行はすべて除外されます。
このようなタイプの結合では、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フィールドの結合に対しては 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値になります。
左外部結合でも内部結合と同様に、別名で設定する必要があります。つまり、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>