select s.id,s.student_name,s.age,c.class_name,c.id from students s
left join class c on s.class_id=c.id order by s.id;
id
student_name
age
class_name
id
1
tom
18
class 1
1
2
jack
19
class 1
1
3
Lucy
18
class 2
2
4
Ely
19
class 3
3
5
Jon
20
class 4
4
6
Messi
19
class 3
3
7
Cl
20
class 3
3
8
UK
22
NULL
NULL
以students表为基准,当class表没有匹配的就为NULL
select c.id,c.class_name,s.id,s.student_name,s.age from class c
left join students s on c.id=s.class_id order by c.id;
id
class_name
id
student_name
age
1
class 1
2
jack
19
1
class 1
1
tom
18
2
class 2
3
Lucy
18
3
class 3
4
Ely
19
3
class 3
6
Messi
19
3
class 3
7
Cl
20
4
class 4
5
Jon
20
5
class 5
NULL
NULL
NULL
Right Join
select s.id,s.student_name,s.age,c.class_name,c.id from students s
right join class c on s.class_id=c.id order by c.id;
id
student_name
age
class_name
id
2
jack
19
class 1
1
1
tom
18
class 1
1
3
Lucy
18
class 2
2
4
Ely
19
class 3
3
6
Messi
19
class 3
3
7
Cl
20
class 3
3
5
Jon
20
class 4
4
NULL
NULL
NULL
class 5
5
select c.id,c.class_name,s.id,s.student_name,s.age from class c
right join students s on c.id=s.class_id order by s.id;
id
class_name
id
student_name
age
1
class 1
1
tom
18
1
class 1
2
jack
19
2
class 2
3
Lucy
18
3
class 3
4
Ely
19
4
class 4
5
Jon
20
3
class 3
6
Messi
19
3
class 3
7
Cl
20
NULL
NULL
8
UK
22
Full Join
Mysql中并不支持Full Join,但我们可以通过以下方法实现Full Join的功能
select s.id,s.student_name,s.age,c.class_name,c.id from students s
left join class c on s.class_id=c.id
union
select s.id,s.student_name,s.age,c.class_name,c.id from students s
right join class c on s.class_id=c.id;
数据<style><!--table, tr, td {border:dotted 1px #ccc}--></style>class表:IDclass_name1 class 12 class 23 class 34 class 45 class 5...