[419]sql语法—left join on 多条件

问题：如果有A表和B表，A表有a1,a2,a3…an字段，B表有b1，b2,b3…bn字段，想查出同时满足条件a1=b1,a2=b2,a3=b3这三个条件的所内容？

用内连接：

select a.*, b.* 
from a 
left join b on a1=b1 and a2=b2 and a3=b3

和楼上那个相比，楼上是在联接的时候就过滤了，我的是联接后过滤，两个结果是不一样的

select a.*, b.* 
from a 
left join b on a1=b1 
where  a2=b2 and a3=b3

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

---

重点

先匹配，再筛选where条件。

本文将通过几个例子说明两者的差别。

表1：product

id	amount
1	100
2	200
3	300
4	400

表2：product_details

id	weight	exist
2	22	0
4	44	1
5	55	0
6	66	1

1. 单个条件

select * from product a
left join on product_details b
on a.id  = b.id

以左表为准匹配，结果：

id	amount	id	weight	exist
1	100	null	null	null
2	200	200	22	0
3	300	null	null	null
4	400	400	44	0

2. 条件写在on 与where区别

查询1：

SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
AND   product.amount=200;

结果：

id	amount	id	weight	exist
1	100	null	null	null
2	200	200	22	0
3	300	null	null	null
4	400	null	null	0

把on的所有条件作为匹配条件，不符合的右表都为null。

查询2：

SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
WHERE product.amount=200;

id	amount	id	weight	exist
2	200	200	22	0

匹配完再筛选，结果只有一条记录。

3. where XXX is null 情况

使用该语句表示：删除掉不匹配on后面条件的记录。
where XXX is not null 则表示筛选出符合on后面条件的记录。
常用于只需要左表的数据，比如count id这类。

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=0
WHERE b.id IS NULL;

结果：

id	amount
1	100
3	300
4	400

可以直观看出，只有id=2的纪录完全匹配上三个条件，所以筛除这条纪录，另三条保留，此时这三条纪录的右表均为null。
筛选出不符合on后面条件的，即 !(a.id=b.id AND b.weight!=44 AND b.exist=0).
!(a.id=b.id AND || !(b.weight!=44) || !(b.exist=0).
(a.id != b.id AND || (b.weight = 44) || ( b.exist! = 0).
逻辑 AND 和 逻辑 OR表达式，其操作数是从左到右求值的。如果第一个参数做够判断操作结果，那么第二个参数便不会被计算求值（短路效果）。

下面语句与该语句效果相同：（这里相同指的是最后只用到左表数据，若是将右表数据写出来是不一样的）

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=1;

将on的否定条件写在where后，效果相同。

注：
如果你使用 LEFT JOIN 来寻找在一些表中不存在的记录，你需要做下面的测试：WHERE 部分的 col_name IS NULL，MYSQL 在查询到一条匹配 LEFT JOIN 条件后将停止搜索更多行（在一个特定的组合键下）。

参考：https://blog.csdn.net/minixuezhen/article/details/79763263