无关的表进行关联查询及null=null条件

零、缘起

若遇到两个表无关联，却又强制关联的情况，可以参考下

考虑到出现条件 null=null的情况写这个文章进行测试下

一、需求

某一个单据采购合同，有多个分录，如商品明细，付款计划等

要求查询所有的合同对应商品，付款计划则拼接名称显示在多个商品的第一行

思路就是让没有关系的表2和表3通过，表1与表2联查获取表2 的entryid（aa表）

表1与表2和表3，三者分组聚合函数联查获取一个表2 的entryid（bb表）

然后aa和bb表通过表1的主键和entryid进行联查

二、初始

自己初始化一些数据，不能用系统的表和数据，复杂且不安全

创建三张表，合同、商品、计划

初始化数据，合同1对应两个商品，两个计划、

合同2对应两个商品两个计划

合同3对应两个计划

create table contract (
fid int primary key,
fname varchar(200)
) engine = innodb;

create table product (
fid int,
fentryid int primary key,
fentryname varchar(200)
) engine = innodb;

create table plan (
fid int,
fpid int primary key,
fpname varchar(200)
) engine = innodb;

INSERT INTO `lane`.`contract` (`fid`, `fname`) VALUES (1, 'c1');
INSERT INTO `lane`.`contract` (`fid`, `fname`) VALUES (2, 'c2');
INSERT INTO `lane`.`contract` (`fid`, `fname`) VALUES (3, 'c3');

INSERT INTO `lane`.`product` (`fid`, `fentryid`, `fentryname`) VALUES (1, 1, 'product1');
INSERT INTO `lane`.`product` (`fid`, `fentryid`, `fentryname`) VALUES (1, 2, 'product2');
INSERT INTO `lane`.`product` (`fid`, `fentryid`, `fentryname`) VALUES (2, 3, 'product3');
INSERT INTO `lane`.`product` (`fid`, `fentryid`, `fentryname`) VALUES (2, 4, 'product4');

INSERT INTO `lane`.`plan` (`fid`, `fpid`, `fpname`) VALUES (1, 1, 'plan1');
INSERT INTO `lane`.`plan` (`fid`, `fpid`, `fpname`) VALUES (1, 2, 'plan2');
INSERT INTO `lane`.`plan` (`fid`, `fpid`, `fpname`) VALUES (2, 3, 'plan3');
INSERT INTO `lane`.`plan` (`fid`, `fpid`, `fpname`) VALUES (2, 4, 'plan4');
INSERT INTO `lane`.`plan` (`fid`, `fpid`, `fpname`) VALUES (3, 5, 'plan5');
INSERT INTO `lane`.`plan` (`fid`, `fpid`, `fpname`) VALUES (3, 6, 'plan6');

三、查询

通过先主表+子表1

再三表聚合联查

两者结果再合并查询即可

 SELECT aa.fid,aa.fname,aa.fentryname,bb.fpname
 from 
(SELECT 
c.fid,c.fname,pt.fentryid,pt.fentryname
from contract c 
left join product pt on c.fid = pt.fid
) aa
LEFT JOIN 
(SELECT 
max(c.fid) fid,max(c.fname) fname,max(pt.fentryid) fentryid,
max(pt.fentryname) fentryname,
max(pp.fpid) fpid,group_CONCAT(distinct pp.fpname) fpname
from contract c 
left join product pt on c.fid = pt.fid
left join plan pp on c.fid = pp.fid
group by c.fid) bb
on aa.fid =bb.fid and aa.fentryid = bb.fentryid

四、结果

测试出现条件 null=null的情况，发现left join 后边的表数据被舍去了

left join会把左表所有数据查询出来，on及其后面的条件仅仅会影响右表的数据(符合就显示，不符合全部为null)。

可以修改下null的情况的条件的值

on aa.fid =bb.fid and ifnull(aa.fentryid,0) = ifnull(bb.fentryid,0)

<=>: 比较操作符(不同于=运算符)，当比较的的两个值为NULL时返回true

修改下SQL

on aa.fid =bb.fid and aa.fentryid <=> bb.fentryid

五、结论

left join on 后的条件 出现 null = null会默认false，舍去掉left join 表查询的所有数据

where null = null 这种写法，也是固定值为false

对于MYSQL可以换为<=>，则条件薇ture