MYSQL连表查询

首先先列举本篇用到的分类(内连接&＃xff0c;外连接&＃xff0c;交叉连接)和连接方法(如下)&＃xff1a;

A&＃xff09;内连接&＃xff1a;join&＃xff0c;inner join

B&＃xff09;外连接&＃xff1a;left join&＃xff0c;left outer join&＃xff0c;right join&＃xff0c;right outer join&＃xff0c;union

C&＃xff09;交叉连接&＃xff1a;cross join

二.下面以实例进行分析

两张假设有两张表格A和B&＃xff0c;把表格当作一个集合&＃xff0c;那么表格中的记录就是集合中的一个元素。

两张表格如下&＃xff1a;

TableA&＃xff1a;TableB&＃xff1a;

2.1 内连接&＃xff08;只有一种场景&＃xff09;

inner join 或者join&＃xff08;等同于inner join&＃xff09;

[java] view plain copy

1. select a.*, b.* from tablea a  
2. inner join tableb b  
3. on a.id &＃61; b.id  

或

[java] view plain copy

1. select a.*, b.* from tablea a  
2. join tableb b  
3. on a.id &＃61; b.id  

结果如下&＃xff1a;

应用场景:

这种场景下得到的是满足某一条件的A&＃xff0c;B内部的数据&＃xff1b;正因为得到的是内部共有数据&＃xff0c;所以连接方式称为内连接。

2.2 外连接&＃xff08;六种场景&＃xff09;

2.2.1 left join 或者left outer join(等同于left join)

[java] view plain copy

1. select a.*, b.* from tablea a  
2. left join tableb b  
3. on a.id &＃61; b.id  

或者

[java] view plain copy

1. select a.*, b.* from tablea a  
2. left outer join tableb b  
3. on a.id &＃61; b.id  

结果如下&＃xff0c;TableB中更不存在的记录填充Null:

应用场景:

这种场景下得到的是A的所有数据&＃xff0c;和满足某一条件的B的数据;

2.2.2  [left   join 或者left outer join(等同于left join)]  &＃43;  [where B.column is null]

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. left join tableb b  
3. on a.id &＃61; b.id  
4. Where b.id is null  

结果如下:

应用场景:

这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据"&＃xff0c;然后得到的A剩余数据&＃xff1b;

2.2.3  right join 或者fight outer join(等同于right join)

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. right join tableb b  
3. on a.id &＃61; b.id  

结果如下&＃xff0c;TableB中更不存在的记录填充Null:

应用场景:

这种场景下得到的是B的所有数据&＃xff0c;和满足某一条件的A的数据&＃xff1b;

2.2.4 [left   join 或者left outer join(等同于left join)]  &＃43;  [where A.column is null]

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. right join tableb b  
3. on a.id &＃61; b.id  
4. where a.id is null  

结果如下:

应用场景:

这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“&＃xff0c;然后得到的B剩余数据&＃xff1b;

2.2.5 full join &＃xff08;mysql不支持&＃xff0c;但是可以用 left join  union right join代替&＃xff09;

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. left join tableb b  
3. on a.id &＃61; b.id  
4. union  
5. select a.id aid,a.age,b.id bid,b.name from tablea a  
6. right join tableb b  
7. on a.id &＃61; b.id  

union过后&＃xff0c;重复的记录会合并&＃xff08;id为2&＃xff0c;3&＃xff0c;4的三条记录&＃xff09;&＃xff0c;所以结果如下&＃xff1a;

应用场景&＃xff1a;

这种场景下得到的是满足某一条件的公共记录&＃xff0c;和独有的记录

2.2.6 full join &＃43; is null&＃xff08;mysql不支持&＃xff0c;但是可以用 &＃xff08;left join &＃43; is null&＃xff09; union &＃xff08;right join&＃43;isnull代替&＃xff09;

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. left join tableb b  
3. on a.id &＃61; b.id  
4. where b.id is null  
5. union  
6. select a.id aid,a.age,b.id bid,b.name from tablea a  
7. right join tableb b  
8. on a.id &＃61; b.id  
9. where a.id is null  

结果如下:

应用场景:

这种场景下得到的是A&＃xff0c;B中不满足某一条件的记录之和

注:上面共有其中七&＃xff08;2^3-1&＃xff09;种应用场景&＃xff0c;还有一种是全空白&＃xff0c;那就是什么都不查&＃xff0c;七种情形包含了实际应用所有可能的场景

2.3 交叉连接 &＃xff08;cross join&＃xff09;

2.3.1 实际应用中还有这样一种情形&＃xff0c;想得到A&＃xff0c;B记录的排列组合&＃xff0c;即笛卡儿积&＃xff0c;这个就不好用集合和元素来表示了。需要用到cross join&＃xff1a;

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. cross join tableb b  

2.3.2 还可以为cross  join指定条件 &＃xff08;where&＃xff09;&＃xff1a;

[java] view plain copy

1. select a.id aid,a.age,b.id bid,b.name from tablea a  
2. cross join tableb b  
3. where a.id &＃61; b.id  

结果如下;

注:这种情况下实际上实现了内连接的效果

三 注意事项

上面仍然存在遗漏&＃xff0c;那就是mysql对sql语句的容错问题&＃xff0c;即在sql语句不完全符合书写建议的情况&＃xff0c;mysql会允许这种情况&＃xff0c;尽可能地解释它:

3.1 一般cross join后面加上where条件&＃xff0c;但是用cross join&＃43;on也是被解释为cross join&＃43;where&＃xff1b;

3.2 一般内连接都需要加上on限定条件&＃xff0c;如上面场景2.1&＃xff1b;如果不加会被解释为交叉连接&＃xff1b;

3.3 如果连接表格使用的是逗号&＃xff0c;会被解释为交叉连接&＃xff1b;

注:sql标准中还有union join和natural  inner join&＃xff0c;mysql不支持&＃xff0c;而且本身也没有多大意义&＃xff0c;其结果可以用上面的几种连接方式得到