MySQL 联表速查
假设有如下两张表:
A:
id | age |
---|---|
1 | 11 |
2 | 12 |
3 | 13 |
4 | 14 |
B:
id | name |
---|---|
2 | aaa |
3 | bbb |
4 | ccc |
5 | ddd |
内连接 (inner join)
只有一种应用场景,inner join或join,等同于inner join
select a.*,b.* from a inner join(join) b on a.id = b.id
结果如下:
id | age | id | name |
---|---|---|---|
2 | 12 | 2 | aaa |
3 | 13 | 3 | bbb |
4 | 14 | 4 | ccc |
外连接
1.left join 或者 left outer join
select a.*,b.* from a left join b on a.id = b.id
结果如下:
id | age | id | name |
---|---|---|---|
1 | 11 | null | null |
2 | 12 | 2 | aaa |
3 | 13 | 3 | bbb |
4 | 14 | 4 | ccc |
2.left join + where b.column is null
select a.*,b.* from a left join b on a.id = b.id where b.id is null
结果如下:
id | age | id | name |
---|---|---|---|
1 | 11 | null | null |
3.right join 或者right out join
select a.*,b.* from a right join b on a.id = b.id
id | age | id | name |
---|---|---|---|
2 | 12 | 2 | aaa |
3 | 13 | 3 | bbb |
4 | 14 | 4 | ccc |
null | null | 5 | ddd |
4.left join + where b.column is null
select a.*,b.* from a right join b on a.id = b.id where a.id is null
id | age | id | name |
---|---|---|---|
null | null | 5l | ddd |
5.left join union right join (可用full join 但mysql不支持)
select a.id aid,a.age,b.id bid,b.name from a
left join b
on a.id = b.id
union
select a.id aid,a.age,b.id bid,b.name from a
right join b
on a.id = b.id
aid | age | bid | name |
---|---|---|---|
1 | 11 | null | null |
2 | 12 | 2 | aaa |
3 | 13 | 3 | bbb |
4 | 14 | 4 | ccc |
null | null | 5 | ddd |
6.(left join + is null) union (right join + is null)
select a.id aid,a.age,b.id bid,b.name from a
left join b
on a.id = b.id
where b.id is null
union
select a.id aid,a.age,b.id bid,b.name from a
right join b
on a.id = b.id
where a.id is null
aid | age | bid | name |
---|---|---|---|
1 | 11 | null | null |
null | null | 5 | ddd |
交叉连接 (cross join)
实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join。
笛卡尔积:笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尓积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
select a.id aid,a.age,b.id bid,b.name from tablea a
cross join tableb b
aid | age | bid | name |
---|---|---|---|
1 | 11 | 2 | aaa |
2 | 12 | 2 | aaa |
3 | 13 | 2 | aaa |
4 | 14 | 2 | aaa |
1 | 11 | 3 | bbb |
2 | 12 | 3 | bbb |
3 | 13 | 3 | bbb |
4 | 14 | 3 | bbb |
1 | 11 | 4 | ccc |
2 | 12 | 4 | ccc |
3 | 13 | 4 | ccc |
4 | 14 | 4 | ccc |
1 | 11 | 5 | ddd |
2 | 12 | 5 | ddd |
3 | 13 | 5 | ddd |
4 | 14 | 5 | ddd |
还可以指定where条件
select a.id aid,a.age,b.id bid,b.name from a
cross join b
where a.id = b.id
aid | age | bid | name |
---|---|---|---|
2 | 12 | 2 | aaa |
3 | 13 | 3 | bbb |
4 | 14 | 4 | ccc |
注意事项:
-
一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where;
-
一般内连接都需要加上on限定条件,如内连接;如果不加会被解释为交叉连接;
-
如果连接表格使用的是逗号,会被解释为交叉连接;
本文摘自:https://blog.csdn.net/Jintao_Ma/article/de...
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: