left join on 后增加条件分析

SQL用过一定时间的同学，对left join，right join应该非常熟悉了，可能有些同学包括我在内，

对left/right join on 后面增加左表或右表条件后，或者多个left/right join 表不同的位置查询结果

感觉到很不解，因此我对以下左连接做了分析演示：

1.两个表的左连接的查询。

2.两个表左连接，在on后面增加左表条件，以及增加右表条件的查询。

3.三张表的左连接的查询(不同的条件)。

备注：这篇文章没有对where后增加条件进行分析，大家只要记住，where后面增加的条件是对临时表生成后，进行过滤的。

而on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

***tab1 left join tab2 on，其实on后面的条件就是对tab2数据的过滤。

 

》》》演示数据脚本

-- 表1
DROP TABLE IF EXISTS `tab1`;
CREATE TABLE `tab1` (
  `id` int(11) DEFAULT NULL,
  `size` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tab1` VALUES ('1', '10');
INSERT INTO `tab1` VALUES ('2', '20');
INSERT INTO `tab1` VALUES ('3', '30');

-- 表2
DROP TABLE IF EXISTS `tab2`;
CREATE TABLE `tab2` (
  `size` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tab2` VALUES ('10', 'AAA');
INSERT INTO `tab2` VALUES ('20', 'BBB');
INSERT INTO `tab2` VALUES ('20', 'CCC');

-- 表3
DROP TABLE IF EXISTS `tab3`;
CREATE TABLE `tab3` (
  `size` int(11) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tab3` VALUES ('10', 'F1');
INSERT INTO `tab3` VALUES ('10', 'F2');
INSERT INTO `tab3` VALUES ('30', 'F3');

》》》演示表数据截图

1.tab1与tab2的左连接

-- 两张表的左连接

-- 查询左表所有记录【包含左表与右表匹配以及左表未匹配的记录】

SELECT
       *
FROM
       tab1
LEFT JOIN tab2 ON tab1.size = tab2.size;

2.tab1与tab2的左连接(on后面增加条件)

-- 很重要：【on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录】。

1)对tab1增加条件

SELECT
       *
FROM
       tab1
LEFT JOIN tab2 ON tab1.size = tab2.size and tab1.id=2;

-- 始终记住left join，左表tab1数据都有，如果加入左表tab1条件tab1.id=2，则只有满足tab1.id=2,对应的右表tab2.size为20的才有数据，其他为null。

-- 重要(相当于只关联符合tab1.id=2的size，其他为null，得到的结果)
 

2)对tab2增加条件与tab1对比

SELECT
       *
FROM
       tab1
LEFT JOIN tab2 ON tab1.size = tab2.size and tab2.name in ('BBB','CCC');

-- 该结果与1)结果一样，左表tab1数据都有，右表tab2数据只有满足tab2.name是BBB和CCC的才有数据，其他为null。

-- 重要(相当于只关联符合tab2.name in (‘BBB’,’CCC’)的size，其他为null，得到的结果)。

 3)对tab2增加的条件

SELECT
       *
FROM
       tab1
LEFT JOIN tab2 ON tab1.size = tab2.size and tab2.`name`='AAA';

-- 左表tab1数据都有，右表tab2数据只有满足tab2.name是AAA的才有数据，其他为null。

-- 重要(相当于只关联符合tab2.name=’AAA’的size，其他为null，得到的结果)。

3.三张表的左连接(下面两种写法结果一样)

-- tab3与tab1、tab2左连接【条件为tab1.size=tab3.size】，

-- 这种写法指，从tab1.size是否在tab3中有匹配数据，如果tab1.size为null，那tab3.size就为null

-- 第二种写法可以看作两张表的左连接。

SELECT
       *
FROM
       tab1
LEFT JOIN tab2 ON tab1.size = tab2.size
LEFT JOIN tab3 ON tab1.size = tab3.size;

SELECT
       *
FROM
       (
              SELECT
                     tab1.size
              FROM
                     tab1
              LEFT JOIN tab2 ON tab1.size = tab2.size
       ) a
LEFT JOIN tab3 ON a.size = tab3.size;

4.三张表的左连接(左连接条件不同)

-- tab3与tab1、tab2左连接【条件为tab2.size=tab3.size】，

-- 这种写法指，从tab2.size是否在tab3中有匹配数据，如果tab2.size为null，那tab3.size就为null

-- 这种统计在于tab2没有数据，tab3就没数据。

SELECT
       *
FROM
       tab1
LEFT JOIN tab2 ON tab1.size = tab2.size
LEFT JOIN tab3 ON tab2.size = tab3.size;

注：本文是个人演示总结，非官方理论，如果有错误的地方，还请同学们在评论下方提出，

       我及时修正，为大家提供更好的演示例子。