SQL join 用于把来自两个或多个表的行结合起来,sql join主要包括inner join、 left join 、right join 、full outer join。
先介绍一下表里面的数据。
第一张表 teacher
第二张表 student
首先是inner join,sql如下
select * from student as s inner join teacher as t on s.id = t.id;
执行结果
解释图
left join,又可以写作left outer join ,sql如下
select * from student as s left join teacher as t on s.id = t.id;
执行结果
解释图
right join,又可以写作right outer join,sql如下
select * from student as s right join teacher as t on s.id = t.id;
执行结果
解释
full outer join,mysql不支持full outer join,sql应该写成
SELECT
s.id AS s_id,
s.FIRST_NAME AS s_f,
s.LAST_NAME AS s_l,
t.FIRST_NAME AS t_f,
t.LAST_NAME AS t_l,
t.id AS t_id
FROM
student AS s
LEFT JOIN
teacher AS t ON s.id = t.id
UNION SELECT
s.id AS s_id,
s.FIRST_NAME AS s_f,
s.LAST_NAME AS s_l,
t.FIRST_NAME AS t_f,
t.LAST_NAME AS t_l,
t.id AS t_id
FROM
teacher AS t
LEFT JOIN
student AS s ON s.id = t.id
输出结果
解释
参考:
http://www.w3cschool.cc/sql/sql-join.html
http://*.com/questions/38549/difference-between-inner-and-outer-joins
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
http://*.com/questions/4796872/full-outer-join-in-mysql