MySQL - 连接详解

student 表

id	name
1	小明
2	小花
3	小黄

score 表

id	student_id	score
1	2	10
2	3	20
3	4	30

交叉连接
cross join
交叉连接可不带 on 子句
把表 student 和表 score 的数据进行一个N*M的组合，即笛卡尔积
如本例会产生3*3=9条记录

//用法1
select * from student,score; 
//用法2;
select * from student cross join score;

查询结果：

id	name	id	student_id	score
1	小明	1	2	10
2	小花	1	2	10
3	小黄	1	2	10
1	小明	2	3	20
2	小花	2	3	20
3	小黄	2	3	20
1	小明	3	4	30
2	小花	3	4	30
3	小黄	3	4	30

内连接
inner join
产生student表和score表的交集
内连接可不带 on 子句,当不带 on子句时相当于 交叉连接产生的结果是 student 和 score 的交集

select * from student as a inner join score as b on a.id=b.student_id;

查询结果：

id	name	id	student_id	score
2	小花	1	2	10
3	小黄	2	3	20

外连接
外连接必须带 on 子句

• 左连接
  left join
  产生student表的完全集，而 score表中匹配的则有值，没有匹配的则以null值取代

select * from student as a left join score as b on a.id=b.student_id;

查询结果：

id	name	id	student_id	score
2	小花	1	2	10
3	小黄	2	3	20
1	小明	NULL	NULL	NULL

• 右连接
  right join
  产生score表的完全集，而 student表中匹配的则有值，没有匹配的则以null值取代

select * from student as a right join score as b on a.id=b.student_id;

查询结果：

id	name	id	student_id	score
2	小花	1	2	10
3	小黄	2	3	20
NULL	NULL	3	4	30

USING

USING是连接中on 子句的简写形式
当on子句中关联表的字段相同时可以使用USING
on t1.a = t2.a and t1.b = t2.b 等效于 using(a,b)
使用 * 查询两个表的所有字段时 using中的字段只显示一次

select * from student as a left join score as b using(id);

查询结果：

id	name	student_id	score
1	小明	2	10
2	小花	3	20
3	小黄	4	30

select * from student as a left join score as b on a.id=b.id;

查询结果：

id	name	id	student_id	score
1	小明	1	2	10
2	小花	2	3	20
3	小黄	3	4	30

当查询两个表的共同字段，并且该字段在using子句中时，不会报错，但在on会引起歧义报错

select id,name from student as a left join score as b using(id);

查询结果：

id	name
1	小明
2	小花
3	小黄

select id,name from student as a left join score as b on a.id=b.id;

查询结果：
[Err] 1052 - Column 'id' in field list is ambiguous