SQL语句中ON 与 WHERE 两种条件的区别

SQL语句中ON 与 WHERE 两种条件的区别

​ 开发中，我们很多时候都会用到多表联合查询，这时候会根据查询结果生成一张临时表返回给用户。比如，left join…on，right join…on等，那么这时候，on和where条件有什么区别呢。我们直接从例子中感受其中的不同：

假设有这样两张表：

表1（b1）：

id	name
1	A
2	B
3	C

表2（b2）：

id	name	color
1	A	red
2	A	yellow
3	C	blue

​

现在我们针对上边两张表写两条不同的sql，并分析：

情况一、

SELECT * FROM b1 LEFT JOIN b2 ON b1.name = b2.name WHERE b2.color = 'red'

​ 该sql的执行分两个步骤：

​ （1）、先根据ON条件筛选，即满足 n.name = c.name，得出结果：

b1.id	b1.name	b2.id	b2.name	b2.color
1	A	1	A	red
1	A	2	A	yellow
2	B	null	null	null
3	C	3	C	blue

​ （2）、针对上边的结果，根据WHERE条件进行过滤，满足 WHERE b1.name = ‘A’，得出最终结果：

b1.id	b1.name	b2.id	b2.name	b2.color
1	A	1	A	red

---

情况二、

SELECT * FROM b1 LEFT JOIN b2 ON b1.name = b2.name AND b2.color = 'red'

​ 该sql的执行，直接根据 **b1.name = b2.name AND b2.color = ‘red’**两个条件过滤，这种情况下，**AND后的条件不为真也会返回左表中的记录，右表结果用null表示。**得出结果：

b1.id	b1.name	b2.id	b2.name	b2.color
1	A	1	A	red
2	B	null	null	null
3	C	null	null	null

---

得出结论:

SELECT * FROM b1 LEFT JOIN b2 ON b1.name = b2.name WHERE b2.color = 'red'
SELECT * FROM b1 LEFT JOIN b2 ON b1.name = b2.name AND b2.color = 'red'

​ 造成上边两种结果的原因是因为LEFT JOIN…ON，RIGHT JOIN…ON，FULL JOIN…ON的特殊性，即：

不管ON的条件是否为真，都会返回LEFT、RIGHT表中的记录，而FULL则返回LEFT和RIGHT特性的并集！！！

但有个例外需要特别注意，就是INNER JOIN…ON，这个二货它没有什么特殊性，条件放在ON中和放在WHERE中，返回结果都是一样的！