1、分类
在多表关联查询时，经常要用到连接查询，SQL中连接分为四种：内连接（inner join或join）、左外连接（left outer join）、右外连接（right outer join）、全连接（full outer join）；
其中，除了inner join其余三种都是外连接。
首先建2张表用于后面的讲解用。
表A：合同表
CREATE TABLE `contract` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '合同表主键ID',
`contract_name` varchar(255) DEFAULT NULL COMMENT '合同名称',
`contract_no` varchar(200) DEFAULT NULL COMMENT '合同编号',
`contract_amount` decimal(11,2) DEFAULT NULL COMMENT '合同金额',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
表B：合同金额历史修改记录表
CREATE TABLE `contract_history` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '历史合同金额表主键ID',
`fk_contract_id` int(10) NOT NULL COMMENT '合同ID',
`hi_contract_amount` decimal(10,2) DEFAULT NULL COMMENT '合同历史金额',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
其中，B表中的fk_contract_id字段与A表中的id字段有外键关系；
2、inner join内连接
内连接结果返回A、B两个表均匹配的数据集，其中有一个表的数据不满足，则那条数据不返回；
模拟数据如下：
A：
image.png
B:
image.png
内连接查询：
select * from contract a INNER JOIN contract_history b on a.id=b.fk_contract_id
结果：
image.png
分析：
1、A表中合同3的数据并没有查询出来，因为B表中没有对应的记录；
2、合同1的数据查出2条了，因为B表中显示合同1修改过2次合同金额了，因此可以看出，当A、B表是一对多的关系时，因inner join没有主从表之分，因此只要A、B表按照连接条件能够匹配的数据都会返回，其中，“一对多”关系中的“一”表数据会有重复，因此使用时需要注意这一点！
3、内连接时，筛选条件放在连接条件on中和放在where中查询的结果集是一致的，但是效率上会有区别，一般来说，放在on中效率更高一些。
3、left outer join左外连接
通常也简称为左连接，sql中可省略outer，写为：left join；
左连接以左表为主表，右表为从表，返回结果集以左表数据为基础，连接右表获取额外信息；
查询语句举例：
select * from contract a LEFT JOIN contract_history b on a.id=b.fk_contract_id
结果：
image.png
分析：
从结果可以看出，虽然合同3在B表中没有数据与之对应，但是还是作为结果集中的一部分返回了，因此可以这么说，只要主表有N条记录，使用left join查询返回的结果集一定至少有N条记录（不考虑where条件）！
这里需要注意：如果A表与B表为一对多关系，查询结果记录数会大于主表（A表）记录数，例如上图中，合同1的记录就有2条；
特别注意左连接时查询条件放置的位置：
使用left join查询时，主表的筛选条件要放在where中，而从表的条件要看情况选择放在连接条件on中或是放在where中；下面举例子说明这两种情况：
栗子1：查询出所有历史合同金额有过300的合同记录。
sql：
SELECT
*
FROM
contract a
LEFT JOIN contract_history b ON a.id = b.fk_contract_id
WHERE
b.hi_contract_amount = 300
结果：
image.png
结果满足我们的需求，但是如果把筛选条件放在on中：
SELECT
*
FROM
contract a
LEFT JOIN contract_history b ON a.id = b.fk_contract_id
AND b.hi_contract_amount = 300
结果：
image.png
显然，查询结果不满足我们的这个需求！
栗子2：查询出所有合同，并且如果其历史合同金额有过300则展示其合同记录信息。
在这个需求下，我们把查询条件放在on中就是正确的，这时是不能放在where中的。
4、right outer join右外连接
通常简称为右连接，outer可省略，写为：right join；
与左连接相反，右连接以右表为主表，即以右表记录为基础，扩展查询左表信息；
一般可与左连接相互转换，例如上栗1用右连接可以写成如下sql：
SELECT
*
FROM
contract_history a
RIGHT JOIN contract b ON a.fk_contract_id = b.id
WHERE
a.hi_contract_amount = 300
结果：
image.png
连接条件同样需要注意位置，可总结为：
1、主表条件放在where中，从表条件按照需求选择放在on中或是where中！
2、当从表条件放在on中时，该条件不影响返回的结果集记录数，但是放在where中则会筛选主表记录！
5、full outer join全连接
全连接，即只要其中某个表存在匹配，full join关键字就会返回行。在mysql中并不支持full join，但可以用left join和right join查询后再union代替，举个栗子：
A表数据：
image.png
B表数据：
image.png
sql：
SELECT
*
FROM
contract a
LEFT JOIN contract_history b ON b.fk_contract_id = a.id
UNION
SELECT
*
FROM
contract a
RIGHT JOIN contract_history b ON b.fk_contract_id = a.id
结果：
image.png
分析：
从结果中可以看出，全连接会返回A、B表中所有记录，其中有关联的会根据on条件进行整合，没有连接上的记录也会返回。
总结：
SQL中的连接查询使用非常频繁，但其中的一些细节还需要平时多注意，比如：
1、左、右连接时的条件放置位置及其区别；
2、一对多关系时，在左、右外连接时，若“一”为主表（或内连接）时，查询结果集主表记录会有重复！若要统计主表记录时则需去重。
以上是笔者在平时工作中用到sql的连接查询时总结出来的知识，分享给大家，希望对读者有帮助！如有错误请给我留言，我会及时更正，谢谢！
网友评论