on与where区别

Question

and a2.ftype=3是必须的条件，其实相当于a2和a1内连接
select a1.fname,a2.fvalue
            from t2 a1
            left join t2 a2 on a1.fid=a2.pid
            where a1.ftype=2 and a2.ftype=3and a2.ftype=3非必要条件，可以查询出不满足a1.fid=a2.pid and a2.ftype=3条件的记录。
select a1.fname,isnull(a2.fvalue,'') as fvalue
from t2 a1
left join t2 a2 on a1.fid=a2.pid and a2.ftype=3
where a1.ftype=2

Answer 1

这个详细的，你有空去看下，SQL查询的逻辑顺序，你可很明了的了解处理方式和顺序这里针对你的问题，我觉得可以这样说，ON和WHERE的区别本质在内连接和外连接的区别，如果你的连接不是左连接而是内连接，那么，条件放到ON和放到WHERE是一样的
而外连接，如左连接，左连接分为左输入和右输入，SQL查询将两个输入根据ON后面的条件过滤出一个积，然后再把左输入存在的而右输入不存在的加入进来，然后再执行WHERE条件过滤
我的描述可能不是完全官方标准，但是，接近了，大体意思就是这样，SQL查询的逻辑处理顺序。
因此，从上面我们可以推出，a2.ftype=3放在WHERE上，过滤的是最终的结果，而放到左连接只取到限制右输入的作用

Answer 2

2楼正解。
on a1.fid=a2.pid  where a1.ftype=2 and a2.ftype=3 过滤最终结果。
on a1.fid=a2.pid and a2.ftype=3  where a1.ftype=2 限制又输入。

Answer 3

select a1.fname,isnull(a2.fvalue,'') as fvalue
from (select fid,fname from t2 where ftype=2) a1
left join (select pid,fvalue from t2 where ftype=3) a2
on a1.fid=a2.pid这样子是可以得到3条记录，也是我所需要的，而且这个我也能理解，只是有些麻烦

Answer 4

select a1.fname,a2.fvalue
            from t2 a1
            left join t2 a2 on a1.fid=a2.pid
            where a1.ftype=2 and a2.ftype=3它不是先做筛选，然后在两个临时表中做Left Join吗？

Answer 5

不是，理解SQL查询可以从逻辑上和物理上看，逻辑上肯定不是，物理上对于左连接也不是

Answer 6

你说的对，我现在想了一下，它应该是在最后的结果集中再执行where条件指定的筛选动作
所以我想使用以下写法，
select a1.fname,isnull(a2.fvalue,'') as fvalue
            from t2 a1,t2 a2
            where a1.ftype=2 and a2.ftype=3 and a1.fid*=a2.pid似乎join可以完全不用

Answer 7

where 是结果集的一个条件过滤，
 on 是对联合多张表的条件关联。on 的在时间顺序要优先于 where 。

Answer 8

在2008中，提示*=不是标准写法，后续将不支持
select a1.fname,isnull(a2.fvalue,'') as fvalue
            from t2 a1
            left join t2 a2 on a1.fid=a2.pid and a2.ftype=3
            where a1.ftype=2
我总觉得这跟我的思维有些冲突

Answer 9

以下步骤显示 SELECT 语句的处理顺序
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE 或 WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Answer 10

在SQL2005就废弃了，要支持需要改数据库兼容性为80以前的版本
*= 和 =* 外部联接运算符
使用 FROM 子句的 JOIN 语法。

Answer 11

这是个过程，得适应
-------------------------------------
我问你，你判断结果对不对的时候是不是先筛选后比对(Join)

Answer 12

这段我很有印象
JOIN 的时候，会先用ON过滤不合适的，然后再JOIN，最后再WHERE，这是正常的处理顺序，即逻辑上的处理顺序，但是，SQL查询优化在内连接的时候，会提前把WHERE的条件带进去过滤，因为这样的结果一样，而且会更有效率，而是物理处理上真正的处理顺序
但是，如果是外连接，提前把WHERE带入过滤得出的结果是不对的，所以左外连接的时候，不会提前把右输入的过滤条件带进去，只会在JOIN后进行筛选，然后，左输入存在而右输入不存在的情况，会把左输入的数据加上右输入的全部设置为NULL加到JOIN后的积，这时在进行WHERE的筛选，不符合条件及涉及NULL的判断全部是FALSE，全部会被过滤掉

Answer 13

你问的问题，在这样的场景下，我不能只回答你，是不是~
ON后面的条件是
WHERE后面的条件不一定是

Answer 14

只有 inner join 才可以等价看成先筛选出子集、再连接出结果。
其实所有的 join 你都应该看成遍历游标的双层循环：
select a1.fname,a2.fvalue
from t2 a1
left join t2 a2 on a1.fid=a2.pid
where a1.ftype=2 and a2.ftype=3
外层游标 SELECT * FROM t2 a1 WHERE a1.ftype=2，循环：
　　内存游标 SELECT * FROM t2 a2 WHERE a1.fid=a2.pid，由于是左连接，不加条件 a2.ftype
　　如果 a2 为空，输出有 a1 无 a2 的记录；
　　如果 a2.ftype=3，输出 a1、a2 都有的记录；
　　如果 a2.ftype<>3，不输出记录。
select a1.fname,isnull(a2.fvalue,'') as fvalue
from t2 a1
left join t2 a2 on a1.fid=a2.pid and a2.ftype=3
where a1.ftype=2
外层游标一样是 SELECT * FROM t2 a1 WHERE a1.ftype=2，循环：
　　内存游标 SELECT * FROM t2 a2 WHERE a1.fid=a2.pid and a2.ftype=3，由于是on条件，可以加在这里
　　如果对应 a2.pid 本来就没记录，内游标为空，输出有 a1 无 a2 的记录；
　　如果对应 a2.pid 的记录 a2.ftype=3，一样输出 a1、a2 都有的记录；
　　如果对应 a2.pid 的记录全部 a2.ftype<>3，内游标为空，输出有 a1 无 a2 的记录。

Answer 15

on是在join时做筛选用的
where是对最后的结果集做筛选的

Answer 16

首先非常感谢ky_min和Tiger_Zhao的详尽回复
不过你们解释的是写出来的语句逻辑对不对不过，以我的思维(我是说用肉眼判断）是
1.select * from t2 a1 where a1.ftype=2
2.select * from t2 a2 where a2.ftype=3
3.用肉眼一一比对，它实际上是双层循环我觉得大家应该都是这样做的吧，这里a1.ftype=2与a2.ftype=3地位是相等的，
结果生成语句的时候要把a2.ftype=3放在on子句后，原因是on先于where执行
可是我们分析问题时肯定先where后join

Answer 17

有规范的东西，你就规范来，肯定是没有问题的， *= ,（+） ， 这些语法，看到了知道是怎么回事就可以了，最好不要那样写，没准儿哪天就不支持了。最开始我用vb6的时候总是写if varBool=true 后来就学人家写成if varBool，不过现在我还是写成if varBool=true

Answer 18

我没有一句说过逻辑不对吧！
-----------------------------------------　
我是说我的逻辑与Sql语句有些不同select a1.fname,isnull(a2.fvalue,'') as fvalue
    from (select fid,fname from t2 where ftype=2) a1
    left join (select pid,fvalue from t2 where ftype=3) a2
        on a1.fid=a2.pid
与
select a1.fname,isnull(a2.fvalue,'') as fvalue
            from t2 a1
            left join t2 a2 on a1.fid=a2.pid and a2.ftype=3
            where a1.ftype=2
有没有不同，我看它们的执行计划似乎是一样的

Answer 19

本身没什么区别，只会在执行顺序上有变化，计划相同用以下方法查看
SET SHOWPLAN_ALL ON;
go