联接分为:
内联接 [inner join]
外联接 (左外联接,右外联接) [left join/left outer join, right join/right outer join]
完全联接 [full join]
交叉联接 [cross join]
a表 id ebcid name b表 uid ebcid
1 1 aa 1 1
2 2 bb 1 2
3 3 cc 1 3
4 4 dd 2 2
5 5 ee 2 3
内联接
select a.*, b.* from a inner join b on a.ebcid = b.ebcid
结果:
1 1 aa 1 1
2 2 bb 1 2
3 3 cc 1 3
2 2 bb 2 2
3 3 cc 2 3
左外联接
select a.*, b.* from a left join b on a.ebcid = b.ebcid
1 1 aa 1 1
2 2 bb 1 2
2 2 bb 2 2
3 3 cc 1 3
3 3 cc 2 3
4 4 dd
5 5 ee
右外联接(以右表为主,例子不够典型~~囧)
select a.*, b.* from a right join b on a.ebcid = b.ebcid
1 1 aa 1 1
2 2 bb 1 2
3 3 cc 1 3
2 2 bb 2 2
3 3 cc 2 3
完全联接
左右都是全的
交叉联接 (笛卡尔积)