Student
id | name | tid |
---|---|---|
1 | zhangsan1 | 1 |
2 | zhangsan2 | 1 |
3 | zhangsan3 | 2 |
4 | zhangsan4 | NULL |
Teacher
id | name |
---|---|
1 | zhangsan_t1 |
2 | zhangsan_t2 |
3 | zhangsan_t3 |
1个学生只能有1个授业恩师,
1个授业恩师可以收多个学生。
查询所有学生的恩师名字
select
s.name,t.name
from student s
join teacher t
on s.tid = t.id;
Student的恩师
name | teacher’s name |
---|---|
zhangsan1 | zhangsan_t1 |
zhangsan2 | zhangsan_t1 |
zhangsan3 | zhangsan_t2 |
发现了什么?
zhangsan4咋没有恩师?
即使张三4没有恩师,也要查到张三4的记录,怎么办?
用外连接
->用左外连接
select
s.name,t.name
from student s
left join teacher t
on s.tid = t.id;
Student的恩师
name | teacher’s name |
---|---|
zhangsan1 | zhangsan_t1 |
zhangsan2 | zhangsan_t1 |
zhangsan3 | zhangsan_t2 |
zhangsan4 | NULL |