left outer join的on不起作用

left outer join的on不起作用

Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
https://stackoverflow.com/questions/15706112/why-and-when-a-left-join-with-condition-in-where-clause-is-not-equivalent-to-the

https://msdn.microsoft.com/zh-cn/library/ms177634.aspx?f=255&MSPPError=-2147217396
https://social.msdn.microsoft.com/Forums/zh-CN/e1198287-96d5-4e9e-b1d0-d2d4f5ba4e20/inner-joinonwhere?forum=sqlserverzhchs

https://dev.mysql.com/doc/refman/5.7/en/join.html
https://dev.mysql.com/doc/refman/5.7/en/outer-join-simplification.html

不管是mysql和sqlserver都一样

SELECT
u.* ,fm.*
FROM
tt1 u
LEFT JOIN tt2 fm
ON u.name = fm.name
and u.CID = 901
这些写and u.CID = 901 不起作用的

SELECT
u.* ,fm.*
FROM
tt1 u
LEFT JOIN tt2 fm
ON u.name = fm.name
where u.CID = 901
这样写where u.CID = 901 才起作用
我查了一下msdn
https://social.msdn.microsoft.com/Forums/zh-CN/e1198287-96d5-4e9e-b1d0-d2d4f5ba4e20/inner-joinonwhere?forum=sqlserverzhchs
没搞明白，为何and u.CID = 901 不起作用

如果是inner join的话

SELECT
u.* ,fm.*
FROM
tt1 u
inner JOIN tt2 fm
ON u.name = fm.name
and u.CID = 901
这样写and u.CID = 901会起作用的

PASS BEIJING Chapter群里某人的说的

left join ，on里面的条件，不管真假，左表都全部保留，包括 u.CID <> 901  的数据

---

http://www.cnblogs.com/lyhabc/articles/3236210.html

CREATE TABLE t1(c1 INT IDENTITY(1,1) PRIMARY KEY,c2 int)
CREATE TABLE t2(c1 INT IDENTITY(1,1) PRIMARY KEY,c2 int)

--INSERT dbo.t2
--        ( c2 )
--VALUES  ( 2  -- c2 - int
--          )

SELECT * FROM dbo.t1
SELECT * FROM dbo.t2

SELECT * FROM dbo.t1
INNER JOIN dbo.t2
ON  t2.c1 = t1.c1
WHERE t2.c1=2

SELECT * FROM t2 WHERE c1<>2

SELECT * FROM dbo.t1
LEFT JOIN dbo.t2
ON  t2.c1 = t1.c1
and t2.c1<>2

在两张不同的表做连接有3种join类型

1、full join
2、inner join
3、outer join(left outer join、right outer join)

在上面两个例子中我们看到的是inner join。如果我们连接表自身就叫做self join。这个特殊类型不会混淆连接类型。

FULL JOIN:full join和笛卡尔有些不同，笛卡尔积会获取所有可能的结果。而full join将匹配的结果与所有左边的表中不匹配右边的行和右边的表中所有不匹配左边的行加在一起，
在不匹配的地方使用NULL代替。结果行数=匹配行数+左表剩余行数+右表剩余行数。（我的理解就是左表行数+右表行数）

LEFT JOIN :左连接（left join）保证左表中的所有行都有，而当不匹配的时候以NULL填充右表字段

rigth JOIN :反过来，右连接（right join）保证右表中所有的行都有，而当不匹配的时候以NULL填充左表字段。

INNER JOIN :就是只列出匹配的行

SELF JOIN:表连接自身叫做self join。为了解释一下这个让我们看如下图中的employee表。EmployeeID是此表的主键，
ReportsTo引用了此表的主键。我们可以想象成这样，ReportTo字段引用代表该雇员的上司，其上司同样也是雇员

---

SQL查询优化 LEFT JOIN和INNER JOIN
http://www.cnblogs.com/VerySky/articles/2180077.html

Left.join优化规则的研究.doc:
一、概述
对于left join的优化，是应用开发人员、数据库内核开发人员关注的问题之一。
应用开发人员关注是因为：并不是每个数据库的内核都支持left join的内部转化，这时候需要应用开发人员进行手工地转化。
内核开发人员关注是因为：并不假定每个应用开发人员都能够熟练地将left join转化掉。因此数据库有必要对这种情况，进行数据库内部的优化。
我当初对left join进行分析归纳，后来阅读mysql时发现 sql_select.cpp文件中的simplify_joins()函数的实现方法也是这样的，大家可以参考该函数。

二、left join优化规则的研究
t1 left t2 on t1.col1=t2.col1
对于类似的表达式，在什么样的情况下才可以去掉left join呢?
我们首先创建三张表：
create table t1(c1 int,c2 int);
create table t2(d1 int,d2 int);
create table t3(e1 int,e2 int);
2.1 优化的基本策略
对于left join的查询语句，比如：
select * from t1 left join t2 on t1.c1=t2.d2 where condition1 [{and conditonN}];(N的取值为2,3,……) (语句1)
什么情况下，才能优化为语句：
select * from t1 inner join t2 on on t1.c1=t2.d2 where condition1 [{and conditonN}]; (语句2)
备注：语句2等价于语句：
select * from t1,t2 where t1.c1=t2.d2 and condition1 [{and conditonN}]; (语句3)
回答：
只要where中的至少有一个conditionK(N的取值为 1,2,……)满足如下非NULL条件，就可以将语句1优化为语句2(语句3)：
1)conditionK包含t2表的列(任意列)
2)conditionK的类型只要不为： t2.column is null。
其它的任何类型都行：比如t2.d2=t1.c2,再比如t2.d2 is not null。
例1：
select * from t1 left join t2 on t1.c1=t2.d2 where t2.d1=2; (t2.d1=2满足非NULL条件，可以优化)
<==>等价于： select * from t1 inner join t2 on t1.c1=t2.d2 where t2.d1=2;
<==>等价于: select * from t1,t2 where t1.c1=t2.d2 and t2.d1=2;
例 2：select * from t1 left join t2 on t1.c1=t2.d2 where t2.d1+1>t1.c1; (t2.d1+1>t1.c1满足非NULL条件，可以优化)
<==>等价于： select * from t1 inner join t2 on t1.c1=t2.d2 where t2.d1+1>t1.c1;
<==>等价于： select * from t1,t2 where t1.c1=t2.d2 and t2.d1+1>t1.c1;
2.2思路扩展
a left join b on condition1 {and conditionM}
left join c on contion2_1 {and contion2_N}
--优化的思路和上文提出的观点完全一样。
例3：
select * from t1 left join t2 on c1=d1 left join t3 on d2=e1 where e1=1; (e1满足非NULL条件，可以优化,甚至这里可以为：e2 in (select ……))
<==>等价于：select * from t1 left join t2 on c1=d1 inner join t3 on d2=e1 where e1=1; //inner转换
<==>等价于：select * from t1 left join t2 on c1=d1,t3 where d2=e1 and e1=1; //等价调整,然后(d2=e1满足非NULL条件，可以优化)
<==>等价于：select * from t1 inner join t2 on c1=d1,t3 where d2=e1 and e1=1; //inner转换
<==>等价于：select * from t1,t2,t3 where c1=d1 and d2=e1 and e1=1;

左表过滤规则
--一个是先WHERE 再JOIN
SELECT * FROM dbo.t1
LEFT JOIN dbo.t2
ON  t2.c1 = t1.c1
WHERE t1.c1<>2

--ON过滤无效
SELECT * FROM dbo.t1
LEFT JOIN dbo.t2
ON  t2.c1 = t1.c1
AND t1.c1<>2

右表过滤规则
--一个是先JOIN 再WHERE
SELECT * FROM dbo.t1
LEFT JOIN dbo.t2
ON  t2.c1 = t1.c1
WHERE t2.c1<>2

--一个是先ON 然后再JOIN
SELECT * FROM dbo.t1
LEFT JOIN dbo.t2
ON  t2.c1 = t1.c1
and t2.c1<>2

---

http://blog.csdn.net/peterxiaoq/article/details/51163082

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

只在左表中存在，不存在在右表的记录
SELECT * FROM tt1 LEFT JOIN tt2 ON tt1.id=tt2.id
WHERE tt1.id IS NULL OR tt2.id IS NULL

同理，还可以模拟inner join. 如下：
select * from tt1 left join tt2 on tt1.id=tt2.id where tt1.id is not null and tt2.id is not null;

mysql没有full outer join，需要用union代替
FULL JOIN的结果跟CROSS JOIN是一样的
SELECT * FROM tt1 FULL JOIN tt2

http://blog.csdn.net/ochangwen/article/details/52346610

f