sql的join用法
浏览数：179 /
时间：2015年06月12日
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://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
