SQL学习十、联结表

作者: 沐左 | 来源:发表于2018-10-24 10:49 被阅读0次

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。

创建联结

1、where子句联结

例如，我们想查询每个订单对应的供应商，对应字段分别在两个表中，我们可以这样写：

select orderNo, supplier
from supplier_new , oderlist_new
where oderlist_new.supplierId = supplier_new.id

或者这样写：

select o.orderNO, s.supplier
from supplier_new as s , oderlist_new as o
where s.id = o.supplierId

查询结果

SQL分析

SELECT语句指定要检索的列，orderNo--订单编号，supplier--供应商，与之前不同的是这次检索的两列分别位于两张表中，orderNo 在oderlist_new表中，supplier 在supplier_new 表中；
FROM 子句，与之前不同的是这次的检索是两张表，所以FROM后面列出了两个表supplier_new , oderlist_new，它们就是这条 SELECT 语句联结的两个表的名字；
WHERE 子句，这两个表用 WHERE 子句正确地联结，WHERE 子句指示DBMS将 oderlist_new表中的 supplierId 与 supplier_new表中的 id匹配起来。

在一条 SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示 DBMS如何对表进行联结的内容。

在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对，WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。
没有 WHERE 子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起（上述查询去掉WHERE 子句会查出36条数据，笛卡儿积。）。

去掉WHERE 子句，笛卡儿积

重要：

要保证所有联结都有 WHERE 子句，否则 DBMS将返回比想要的数据多得多的数据
要保证 WHERE 子句的正确性。不正确的过滤条件会导致 DBMS返回不正确的数据

2、内联结（inner join）

上面使用where 子句进行的联结称为等值联结（equijoin），它基于两个表之间的相等测试，也称为内联结（inner join）。

示意图

上面使用where 子句进行表的联结，使用的是简单的等值语法，标准格式应该是使用 INNER JOIN （内联结）语法，如下：

select orderNo, supplier
from supplier_new inner join oderlist_new
on oderlist_new.supplierId = supplier_new.id

不同：

FROM 子句，两个表之间的关系是以 INNER JOIN 指定的部分 FROM 子句（联结的表）
ON 子句，联结条件用特定的 ON 子句而不是 WHERE 子句给出

注意：不要联结过多的表
SQL 不限制一条 SELECT 语句中可以联结的表的数目，但实际上许多 DBMS 都有限制，而且不要联结不必要的表。联结的表越多，性能下降越厉害。因为 DBMS 在运行时要关联指定的每个表，以处理联结，而这种处理可能非常耗费资源。

3、自联结（self-join）

例如，我们需要查询和订单号20180831013同一供应商的所有订单信息（两次使用到同一张表），我们可以这样写：

使用子查询
我们先在订单表oderlist_new中查出orderNo = '20180831013'订单的供应商Id（supplierId），
然后在订单表oderlist_new中查出相同供应商Id（supplierId）的所有订单。

select * 
from oderlist_new 
where supplierId = (
    select supplierId 
    from oderlist_new
    where orderNo = '20180831013'
)

查询结果

使用联结（自联结）
下面的SQL中使用了o1.*，如果直接查询*的话，会将oderlist_new 查询两遍，即每行的字段从id到supplierId都会展示两遍，因为直接查询*，表示查询from后面关联的所有表的所有字段，所以下面的SQL会展示o1,o2两个表的所有字段，但其实我们o1,o2都是查询的oderlist_new 这个表，所以每行的数据都会重复一遍。
故，我们在使用联结的时候需要明确指定返回的数据

select o1.* 
from oderlist_new o1, oderlist_new o2
where o2.orderNo = '20180831013' and o1.supplierId = o2.supplierId

查询结果

select * 
from oderlist_new o1, oderlist_new o2
where o2.orderNo = '20180831013' and o1.supplierId = o2.supplierId

查询结果

4、外联结（OUTER JOIN）

在表联结中，包含了那些在相关表（联结的表）中除关联行（关联关系没有关联到的行）之外的行，这种联结称为外联结。
外联接可以是左外联结（left join）、右外联结（right join）或全外联结（full join）

5、左外联结（left join）

LEFT JOIN 从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

白话解释：左外联结就是取左表的所有行，去匹配右表中符合条件的数据，有就取出来完善数据，没有就置为NULL

例如，我们用左外联结（left join）来查询订单表中每个订单对应的供应商

首先，我们来看下订单表（oderlist_new ）和供应商表（supplier_new ）中的数据

订单表（oderlist_new ）

订单表

供应商表（supplier_new ）

供应商

使用SQL查询：

oderlist_newleft joinsupplier_new

select o.orderNO, s.supplier
from oderlist_new o left join supplier_new s 
on s.id = o.supplierId

查询结果

supplier_newleft joinoderlist_new

select o.orderNO, s.supplier
from supplier_new s left join oderlist_new o
on s.id = o.supplierId

查询结果

SQL分析
可以看到在from 子句中调换 左外联结（left join）左右表之后查询到的结果是不一样的，
这样我们就可以理解上面对 左外联结（left join） 的解释了。

oderlist_newleft joinsupplier_new，订单表（oderlist_new ）和供应商表（supplier_new ）进行左外联结，订单表（oderlist_new ）在左边，所以从订单表（oderlist_new ）中取全部记录（如上，共18条），然后去供应商表（supplier_new ）中找符合on条件的对应行，故查询出18条数据
supplier_newleft joinoderlist_new，供应商表（supplier_new ）和订单表（oderlist_new ）进行左外联结，供应商表（supplier_new ）在左边，所以从供应商表（supplier_new ）中取全部记录（如上，共3条），然后去订单表（oderlist_new ）中找符合on条件的对应行，故查询出了19条数据，且最后一条数据是没有订单号的（订单表（oderlist_new ）中没有杭州有机蔬菜专供经销商对应的订单）

所以，我们就知道了 左外联结（left join），就是取左边表的全部数据，然后右边表中找符合条件的数据。

6、右外联结（right join）

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

改变联结表的顺序， 左外联结（left join）和右外联结（right join）可以等效替换。

7、全外联结（full join）

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。

与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。

注意：

1、Access、MariaDB、MySQL、Open Office Base和 SQLite都不支持 FULL OUTER JOIN 语法。

2、虽然终的结果是相同的，但许多 DBMS处理联结远比处理子查询快得多。至于应该选择哪种方法，可以试一下两种方法，以确定哪一种的性能更好。

联结和聚集函数一起使用

我们在联结中使用聚合函数对多个表进行聚合运算

上一篇中（讲子查询的那一节）我们对比了查询相同结果，在不同地方使用子查询的SQL执行效率对比，这里我们就使用联结查询来和子查询对比一下，看看那个效率更快一点。

和上篇的子查询的SQL功能一样，我们同样是获取国家内业图斑中图斑面积大于10亩的的图斑分别拥有的附件数量

子查询
SQL[0]

//执行1次子查询
select W.TBBH, count(*) As FJ_AMOUNT
From WYHCFJ As W
where W.TBBH in (
    select SR.TBBH 
    From SURVEY_RECORD AS SR
    Where SR.TBMJ > 10 and SR.TBLX = 'GJNYTB'
) and W.TCBM = 'GJNYTB'
group by W.TBBH

查询结果

内联结
使用内联结在这份数据中查询的效率和子查询有偏差，这也就回到了上面提到的注意事项了，联结和子查询的效率问题最好自己进行一下验证。

SQL[1]--- WYHCFJ W inner join SURVEY_RECORD SR

select W.TBBH, count(*) As FJ_AMOUNT
from WYHCFJ W inner join SURVEY_RECORD SR
on SR.TBMJ > 10 and SR.TBLX = 'GJNYTB' and W.TCBM = 'GJNYTB' and W.TBBH = SR.TBBH 
group by W.TBBH

查询结果

SQL[2]--- SURVEY_RECORD SR inner join WYHCFJ W

可以看到使用内联结时调换联结表的位置对查询出的结果并没有影响，因为内联结取的是“交集”

select SR.TBBH, count(*) As FJ_AMOUNT
from SURVEY_RECORD SR inner join WYHCFJ W 
on SR.TBMJ > 10 and SR.TBLX = 'GJNYTB' and W.TCBM = 'GJNYTB' and W.TBBH = SR.TBBH 
group by SR.TBBH

查询结果

内联结是查询两张表中关联的数据行，所以查出来的结果是一样的，但是用外联结查询的结果就会有些不同

左外联结

我们将上面的内联结改为左外联结

SQL[3]

select SR.TBBH, count(W.F_ID) As FJ_AMOUNT
from SURVEY_RECORD SR left join WYHCFJ W 
on SR.TBMJ > 10 and SR.TBLX = 'GJNYTB' and W.TCBM = 'GJNYTB' and W.TBBH = SR.TBBH 
group by SR.TBBH

查询结果

SQL分析：
我们发现直接将内联结更换为左外联结查询出来的结果不是正确结果，为什么呢？
我们先分析一下上面这个SQL，上面这个左联结的SQL，会先取SURVEY_RECORD表中的所有数据（167行），然后用这167行去关联WYHCFJ表进行查询符合on子句中条件的相关数据，所以很多TBBH 的符合条件的附件数量都是0。

SQL[4] 我们调换表联结的顺序

select SR.TBBH, count(W.F_ID) As FJ_AMOUNT
from WYHCFJ W left join SURVEY_RECORD SR 
on SR.TBMJ > 10 and SR.TBLX = 'GJNYTB' and W.TCBM = 'GJNYTB' and W.TBBH = SR.TBBH 
group by SR.TBBH

查询结果

SQL分析：
我们看这次的查询结果，和正确的结果多了一条数据，而且TBBH为NULL，数量为580。
这个数据是怎么来的，我们调换表联结的顺序之后，上述SQL的意思就是，先去WYHCFJ表中的所以数据（1074条），然后用这1074条数据去关联SURVEY_RECORD中符合on条件的相关数据，没有符合条件的就置为NULL，所以有79条（获取79组，用TBBH进行的分组）是符合之前条件的数据，得到的结果也是正确的，但是剩下的WYHCFJ表中的数据在SURVEY_RECORD中没有符合条件的数据进行对应，所以查询的SR.TBBH就被置为NULL了，后面的count(W.F_ID) As FJ_AMOUNT统计的就是WYHCFJ表中所有发附件数量减去符合条件的494条数据得到的580条数据。

我们查询一下符合条件的数据有多少条，SQL：

select count(*) As FJ_AMOUNT
From WYHCFJ As W
where W.TBBH in (
    select SR.TBBH 
    From SURVEY_RECORD AS SR
    Where SR.TBMJ > 10 and SR.TBLX = 'GJNYTB'
) and W.TCBM = 'GJNYTB'

查询结果

那么我们怎么利用左外联结查询出正确的结果呢？

SQL[5]-- on 和 where 在联结中的区别

select SR.TBBH, count(W.F_ID) As FJ_AMOUNT
from WYHCFJ W left join SURVEY_RECORD SR 
on SR.TBMJ > 10 and SR.TBLX = 'GJNYTB' 
where W.TCBM = 'GJNYTB' and W.TBBH = SR.TBBH 
group by SR.TBBH

查询结果

SQL分析：
on 条件是在生成临时表时（-ing）使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录；而where 条件是在临时表生成好之后（-ed），再对临时表进行过滤的条件，条件不为真的就全部过滤掉。

也就是说SQL[4]会查出80条数据，是因为我们把条件都写在on条件下，那么这些条件的限制只会在生成（-ing）临时表的时候起作用，并且它不管 on 中的条件是否为真，都会返回左边表中的记录，所以就查询出了80条数据；而SQL[5]我们把W.TCBM = 'GJNYTB' and W.TBBH = SR.TBBH条件写在where条件下，虽然SQL[5]在on条件（SR.TBMJ > 10 and SR.TBLX = 'GJNYTB'）下生成的临时表中有不符合正确结果的其他数据，但是在where条件中我们是将其过滤掉了的，所以可以返回正确的查询结果。

上面的查询时间，我取到的是反应平均水平的众值，相对于端值应该更能反应相关SQL的查询效率。
至于上述SQL的查询效率的区别，主要是表中数据行数的多少，以及条件筛选出来的数据范围。

笔记

1、在使用 jion 时，on 和 where 条件的区别如下：

1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

2、不同的 SQL JOIN

内联结（INNER JOIN）：如果表中有至少一个匹配，则返回行
左联结（LEFT JOIN）：即使右表中没有匹配，也从左表返回所有的行
右联结（RIGHT JOIN）：即使左表中没有匹配，也从右表返回所有的行
全联结（FULL JOIN）：只要其中一个表中存在匹配，则返回行

3、SQLite 外联结

SQLite支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。
**所以当我们需要使用RIGHT OUTER JOIN时，我们可以调整SQL中表的顺序用LEFT OUTER JOIN替代。

SQL学习十、联结表

相关概念

1、关系表

2、可伸缩

3、联结

4、笛卡儿积

5、叉联结

创建联结

1、where子句联结

2、内联结（inner join）

3、自联结（self-join）

4、外联结（OUTER JOIN）

5、左外联结（left join）

6、右外联结（right join）

7、全外联结（full join）

联结和聚集函数一起使用

笔记

1、在使用 jion 时，on 和 where 条件的区别如下：

2、不同的 SQL JOIN

3、SQLite 外联结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SQL学习十、联结表

相关概念

1、关系表

2、可伸缩

3、联结

4、 笛卡儿积

5、叉联结

创建联结

1、where子句联结

2、内联结（inner join）

3、自联结（self-join）

4、外联结（OUTER JOIN）

5、左外联结（left join）

6、右外联结（right join）

7、全外联结（full join）

联结 和 聚集函数一起使用

笔记

1、在使用 jion 时，on 和 where 条件的区别如下：

2、不同的 SQL JOIN

3、SQLite 外联结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

4、笛卡儿积

联结和聚集函数一起使用