select * from students s
left join classes c on s.class_id=c.id
id
name
age
class_id
id(1)
class_name
teacher_id
1
张三
12
1
1
一班
2
2
李四
13
2
2
二班
1
7
阿花
15
2
2
二班
1
3
独孤
16
6
2.内连接
-- 写法1 显式
select * from students s inner join classes c on s.class_id=c.id
-- 写法2 显式 默认
select * from students s join classes c on s.class_id=c.id
-- 写法3 隐式
select * from students s, classes c where s.class_id=c.id
id
name
age
class_id
id(1)
class_name
teacher_id
1
张三
12
1
1
一班
2
2
李四
13
2
2
二班
1
7
阿花
15
2
2
二班
1
3.右连接
select * from students s
right join classes c on s.class_id=c.id
id
name
age
class_id
id(1)
class_name
teacher_id
1
张三
12
1
1
一班
2
2
李四
13
2
2
二班
1
7
阿花
15
2
2
二班
1
3
三班
3
4.左连接 仅左边独有
select * from students s
left join classes c on s.class_id=c.id
where c.id is null
id
name
age
class_id
id(1)
class_name
teacher_id
3
独孤
16
6
5.全连接 左右都包含
select * from students s
left join classes c on s.class_id=c.id
union
select * from students s
right join classes c on s.class_id=c.id
id
name
age
class_id
id(1)
class_name
teacher_id
1
张三
12
1
1
一班
2
2
李四
13
2
2
二班
1
7
阿花
15
2
2
二班
1
3
独孤
16
6
3
三班
3
6.右连接 仅右边独有
select * from students s
right join classes c on s.class_id=c.id
where s.id is null