1:这一条是条件写在最后的WHERE
SELECT *
FROM Pub_Goods AS a LEFT JOIN
Pub_Kind AS b ON a.KindCode = b.Code LEFT JOIN
Pub_Type AS c ON b.TypeCode = c.Code
WHERE (a.Name = '产品名字') AND (b.Name = '分类名字') AND (c.Name = '类型名字')
2:这一条是条件写在On
SELECT *
FROM Pub_Goods AS a LEFT JOIN
Pub_Kind AS b ON a.KindCode = b.Code AND a.Name = '产品名字' AND b.Name = '分类名字' LEFT JOIN
Pub_Type AS c ON b.TypeCode = c.Code AND c.Name = '类型名字'
----------------
上面两条SQL要MS-SQL和Oracle中效率的详细区别
谢谢
PS:前面问了问题是INNER JOIN的情况回答是:
http://www.cnblogs.com/JimmyGe/archive/2011/03/10/1980271.html
这里面说了INNER JOIN效率一样,但Left Join只是说结果不一样,没有说效率问题谢谢
SELECT *
FROM Pub_Goods AS a LEFT JOIN
Pub_Kind AS b ON a.KindCode = b.Code LEFT JOIN
Pub_Type AS c ON b.TypeCode = c.Code
WHERE (a.Name = '产品名字') AND (b.Name = '分类名字') AND (c.Name = '类型名字')
2:这一条是条件写在On
SELECT *
FROM Pub_Goods AS a LEFT JOIN
Pub_Kind AS b ON a.KindCode = b.Code AND a.Name = '产品名字' AND b.Name = '分类名字' LEFT JOIN
Pub_Type AS c ON b.TypeCode = c.Code AND c.Name = '类型名字'
----------------
上面两条SQL要MS-SQL和Oracle中效率的详细区别
谢谢
PS:前面问了问题是INNER JOIN的情况回答是:
http://www.cnblogs.com/JimmyGe/archive/2011/03/10/1980271.html
这里面说了INNER JOIN效率一样,但Left Join只是说结果不一样,没有说效率问题谢谢
是连接条件的,必须写到连接条件里,
是查询条件的,才能写到查询条件里.
最简单的,对Left连接左表的条件可以写到查询条件里,而右表的条件最好都写到连接条件里.
这也是微软为什么推荐使用 join 类型的连接语句,而建议不用 a,b where 类型的连接语句的原因.因为,它可能产生二义性.
on 是仅过滤两个表的连接条件,是一到一,还是多对多,
而where可以理解为是最后再对记录进行总的过滤