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
