表a:
id | name |
1 | a1 |
2 | a2 |
表b:
xxId | name |
2 | b1 |
3 | b2 |
4 | b3 |
5 | b4 |
1)表a内连接表b
select * from a inner join b on a.id = b.xxId(两者为平等的,即舍弃两者均未有的数据)
结果为:
id | name | xxId | name |
2 | a2 | 2 | b1 |
表b内连接表a
select * from b inner join a on b.xxid = a.xxid
xxid | name | Id | name |
2 | b1 | 2 | a2 |
2)表a左外连接表b
select * from a left (outer) join on a.id = b.xxId(因为左连接,即以左侧为基准(决定了有几行),对应填充,对应有值即填值,若不存在填null)
结果为:
id | name | xxId | name |
1 | a1 | NULL | NULL |
2 | a2 | 2 | b1 |
3.表b左外连接表a
select * from b left (outer) join on b.xxId = a.id(同上可知,查询记录共有4行,对应填充)
特注:注意表头展示顺序,以谁为基础谁在前
xxId | name | id | name |
2 | b1 | 2 | a2 |
3 | b2 | NULL | NULL |
4 | b3 | NULL | NULL |
5 | b4 | NULL | NULL |
4.表a右连接表b
select * from a right (outer) join on b where a.id = b.xxId
结果为:
id | name | xxId | name |
2 | a2 | 2 | b1 |
NULL | NULL | 3 | b2 |
NULL | NULL | 4 | b3 |
NULL | NULL | 5 | b4 |
5.表b右连接表a
select * from b right (outer) join on a where b.xxId = a.id
结果为:
xxId | name | id | name |
NULL | NULL | 1 | a1 |
2 | b1 | 2 | a2 |