MySQLでテストします
SQL
1create table tbl_a(aid int unique,xid int);
2insert into tbl_a values(1,11),(2,12),(3,12),(4,14);
3create table tbl_b(bid int unique,yid int);
4insert into tbl_b values(1,21),(2,22),(3,23);
5create table tbl_c(cid int unique,xid int,yid int);
6insert into tbl_c values(1,11,21),(2,11,22),(3,12,21),(4,12,21),(5,12,23),(6,13,21);
tbl_aとtbl_bを無条件に結合
以下動作OK
SQL
1select * from tbl_a as a,tbl_b as b
命題の結合
動作エラー
SQL
1select * from tbl_a as a,tbl_b as b
2inner join tbl_c as c1 on a.xid=c1.xid
3inner join tbl_c as c2 on b.yid=c2.yid
※考えられる理由:tbl_aとtbl_bを結合する条件がないため
(単純な結合ならいいがそれぞれにinner joinがカラムとNG)
SQL
1select * from tbl_a as a
2inner join tbl_b as b on 1
3inner join tbl_c as c1 on a.xid=c1.xid
4inner join tbl_c as c2 on b.yid=c2.yid
tbl_aとtbl_bを先に結合した後、tbl_cをjoinする
SQL
1select * from
2(select * from tbl_a as a,tbl_b as b) as sub
3inner join tbl_c as c1 on sub.xid=c1.xid
4inner join tbl_c as c2 on sub.yid=c2.yid
それぞれjoinしてから無条件結合
※この場合ワルドカードは使えない
SQL
1select * from
2(select aid,a.xid,c1.yid from tbl_a as a inner join tbl_c as c1 on a.xid=c1.xid) as sub1,
3(select bid,b.yid,c2.xid from tbl_b as b inner join tbl_c as c2 on b.yid=c2.yid) as sub2
4