sql的join用法

SQL join 用于把来自两个或多个表的行结合起来,sql join主要包括inner join、 left join 、right join 、full outer join。

先介绍一下表里面的数据。

第一张表 teacher

sql的join用法

第二张表 student

sql的join用法


首先是inner join,sql如下

select * from student as s inner join teacher as t on s.id = t.id;

执行结果

sql的join用法

解释图

sql的join用法


left join,又可以写作left outer join ,sql如下

select * from student as s left join teacher as t on s.id = t.id;

执行结果

sql的join用法

解释图

sql的join用法


right join,又可以写作right outer join,sql如下

select * from student as s right join teacher as t on s.id = t.id;

执行结果

sql的join用法

解释

sql的join用法


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

输出结果

sql的join用法

解释

sql的join用法

参考:

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

上一篇:android uiautomator自己主动化測试


下一篇:HttpClient学习记录-系列1(tutorial)