sql的join用法

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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。