MySql的Join使用

数据

<style> <!-- table, tr, td {border:dotted 1px #ccc} --> </style>

class表:

ID	class_name
1	class 1
2	class 2
3	class 3
4	class 4
5	class 5

students表:

ID	student_name	age	class_id
1	tom	18	1
2	jack	19	1
3	Lucy	18	2
4	Ely	19	3
5	Jon	20	4
6	Messi	19	3
7	Cl	20	3
8	UK	22	6

Join与Inner Join

Join是Inner Join的简写,只有当俩表中都存在的时候才会select

select s.id,s.student_name,s.age,c.class_name,c.id from students s 
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

由于没有student属于class 5和没有class 6,因此UK和class 5并不会被select

Left 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 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;

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
NULL	NULL	NULL	class 5	5