MySql的Join使用

来源：互联网发布：淘宝主宝贝副宝贝意思编辑：程序博客网时间：2024/06/05 05:19

数据

class表:

IDclass_name1 class 12 class 23 class 34 class 45 class 5

students表:

IDstudent_nameageclass_id1tom1812jack1913Lucy1824Ely1935Jon2046Messi1937Cl2038UK226

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;

idstudent_nameageclass_nameid1tom18class 112jack19class 113Lucy18class 224Ely19class 335Jon20class 446Messi19class 337Cl20class 33由于没有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;

idstudent_nameageclass_nameid1tom18class 112jack19class 113Lucy18class 224Ely19class 335Jon20class 446Messi19class 337Cl20class 338UK22NULLNULL以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;

idclass_nameidstudent_nameage1class 12jack191class 11tom182class 23Lucy183class 34Ely193class 36Messi193class 37Cl204class 45Jon205class 5NULLNULLNULL

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;

idstudent_nameageclass_nameid2jack19class 111tom18class 113Lucy18class 224Ely19class 336Messi19class 337Cl20class 335Jon20class 44NULLNULLNULLclass 55

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;

idclass_nameidstudent_nameage1class 11tom181class 12jack192class 23Lucy183class 34Ely194class 45Jon203class 36Messi193class 37Cl20NULLNULL8UK22

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.idunionselect 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;

idstudent_nameageclass_nameid1tom18class 112jack19class 113Lucy18class 224Ely19class 335Jon20class 446Messi19class 337Cl20class 338UK22NULLNULLNULLNULLNULLclass 55