Mysql外连接示例说明demo

select  t.student_name,sc.object,sc.score,st.student_class from student.st,score,sc where st.student_id=sc.student_id

table1:

id	name	pw
1	bob	123
2	aim	456
3	pop	789

table2:

id	user	class
4	bob	01
5	aim	02
6	tom	03

 

 

1. 内联：select * from table1 inner table2 on table1.name=table2.user

id	name	pw	id2	user	class
1	bob	123	4	bob	01
2	aim	456	5	aim	02

 

1. 左连：select * from table1 left (outer) join table2 on table1.name=table2.user

id	name	pw	id2	user	class
1	bob	123	4	bob	01
2	aim	456	5	aim	02
3	pop	789	null	null	null

 

1. 右连：select * from table1 right (outer) join table2 on table1.name=table2.user

id	name	pw	id2	user	class
1	bob	123	4	bob	01
2	aim	456	5	aim	02
null	null	null	6	tim	03

 

1. 全连：select * from table1 lfull (outer) join table2 on table1.name=table2.user

id	name	pw	id2	user	class
1	bob	123	4	bob	01
2	aim	456	5	aim	02
null	null	null	6	Tim	03