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

相关概念

1、关系表

理解关系表，最好可以通过一个例子。

比如我们有这样一张水果订单表oderlist，表中每一行是一个水果订单，里面包含（主键id、水果名、数量、单价、订单号、用户id、用户名称、订单时间、水果供应商、供应商地址、供应商电话、供应商邮箱、供应商联系人）。

水果订单表oderlist

通过表中的数据我们可以看出同一个供应商供应了多个订单，这样存储会产生数据冗余，且不易存储和管理，需要思考将订单信息和供应商信息分开存储。

不足：

• 同一供应商对应的每个订单，其供应商信息都是相同的，对每个订单重复此信息既浪费时间又浪费存储空间
• 如果供应商信息发生变化，例如供应商迁址或电话号码变动，需要对该供应商对应的每个订单都修改一次
• 每个订单都存储供应商信息，则很难保证每次输入该数据的方式都相同，不一致的数据在报表中就很难利用

这里就涉及到一个关系数据库设计的基础 ---- 相同的数据出现多次决不是一件好事。
关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

所以上表的数据我们可以这样存储：
创建两个表，一个存储供应商信息，一个存储订单信息。

供应商表 supplier_new 中存储供应商信息，包含（主键id、水果供应商、供应商地址、供应商电话、供应商邮箱、供应商联系人）

新的水果订单表 orderlist_new 中只存储订单信息，包含（主键id、水果名、数量、单价、订单号、用户id、用户名称、订单时间、水果供应商ID），除了存储水果供应商 ID（supplier_new 表的主键） 外，它不存储其他有关供应商的信息，supplier_new 表的主键将 supplier_new 表 与 orderlist_new 表关联，利用供应商 ID能从 supplier_new 表中找出相应供应 商的详细信息。

新的水果订单表orderlist_new

供应商表supplier_new

这样做的好处：

• 供应商信息不重复，不会浪费时间和空间
• 如果供应商信息变动，可以只更新 supplier_new 表中的单个记录，相关表 中的数据不用改动
• 由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单

总之，关系数据可以有效地存储，方便地处理数据。

2、可伸缩

能够适应不断增加的工作量而不失败。

设计良好的数据库或应用程序可以根据需求的变化不断的调整，且不影响查询或使用效率，不造成数据/代码冗余，称为可伸缩性好。

3、联结

就像上面提到的，在关系数据库中我们将数据存储到不同的表中，那么我们怎么查询多张表中的数据呢？
答案就是使用联结。
简单说，联结是一种机制，用来在一条 SELECT 语句 中关联表，因此称为联结。
使用特殊的语法，可以联结多个表返回一组输出。
联结不是物理实体，它只在查询执行期间存在。

4、 笛卡儿积

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目 将是第一个表中的行数乘以第二个表中的行数。

5、叉联结

有时，返回笛卡儿积的联结，也称叉联结（cross 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替代。