a表:
person_id | persion_name |
1 | aa |
2 | bb |
3 | cc |
b表:
job_id | job_name | parent_id |
1 | 11 | 1 |
2 | 22 | 2 |
3 | 33 | 4 |
1) 内连接(join=inner join)
显示连接:
SELECT a.*,b.* FROM a INNER JOIN b ON a.persion_id=b.persion_id
隐式连接:
SELECT a.*,b.* FROM a,b WHERE a.persion_id=b.persion_id
结果集:
persion_id | persion_name | job_id | job_name | persion_id1 |
1 | aa | 1 | 11 | 1 |
2 | bb | 2 | 22 | 2 |
2) 左连接(left join=left outer join)
SELECT a.*,b.* FROM a LEFT JOIN b ON a.persion_id=b.persion_id
结果集:
persion_id | persion_name | job_id | job_name | persion_id1 |
1 | aa | 1 | 11 | 1 |
2 | bb | 2 | 22 | 2 |
3 | cc | null | null | null |
3) 右连接(right join=right outer join)
SELECT a.*,b.* FROM a RIGHT JOIN b ON a.persion_id=b.persion_id
结果集:
persion_id | persion_name | job_id | job_name | persion_id1 |
1 | aa | 1 | 11 | 1 |
2 | bb | 2 | 22 | 2 |
null | null | 3 | 33 | 4 |
4) 交叉连接
SELECT a.*,b.* FROM a,b
或者
SELECT a.*,b.* FROM a CROSS JOIN b
结果集:(3X3=9条记录)
persion_id | persion_name | job_id | job_name | persion_id1 |
1 | aa | 1 | 11 | 1 |
2 | bb | 1 | 11 | 1 |
3 | cc | 1 | 11 | 1 |
1 | aa | 2 | 22 | 2 |
2 | bb | 2 | 22 | 2 |
...... | ...... | ..... | ..... | ..... |
5) 完全连接(MySql不支持FULL JOIN)
SELECT a.*,b.* FROM a FULL JOIN b ON a.persion_id=b.persion_id
结果集:
persion_id | persion_name | job_id | job_name | persion_id1 |
1 | aa | 1 | 11 | 1 |
2 | bb | 2 | 22 | 2 |
null | null | 3 | 33 | 4 |
3 | cc | null | null | null |
MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
left join + union(可去除重复数据)+ right join
两张表时:
select * from A left join B on A.id = B.id (where 条件)
union
select * from A right join B on A.id = B.id (where条件);