sql语句中Left join的on和where

问题描述：

问题是并了几张表，其中有left join 或者inner join的，比如下面这样

SELECT * from 
(SELECT a.inserttime, a.Num 
FROM test.contract a 
LEFT JOIN test.customer b ON a.Num = b.Num2) tmp 
LEFT JOIN test.customer4 d ON tmp.num = d.num4 ;

然后现在想取出的是a.inserttime > '2019-5-5'的数据，该用where还是on呢？
该写在哪里？是要在最外面再包一层select用where a.inserttime > '2019-5-5'吗？

答

以mysql为例，
查询条件insertime，直接写在括号里，由于有外联，where条件写在括号内的外联后，

SELECT * from 
(SELECT a.inserttime, a.Num 
FROM contract a 
LEFT JOIN customer b ON a.Num = b.Num2
WHERE a.inserttime > '2019-07-28') tmp 
LEFT JOIN customer4 d ON tmp.num = d.num4 ;

答

SELECT * from
(SELECT a.inserttime, a.Num
FROM test.contract a where a.inserttime > '2019-5-5'
LEFT JOIN test.customer b ON a.Num = b.Num2) tmp
LEFT JOIN test.customer4 d ON tmp.num = d.num4

答

用where，写在内部子查询中，类似如下：

SELECT * from 
(SELECT a.inserttime, a.Num 
FROM contract a 
LEFT JOIN customer b ON a.Num = b.Num2
WHERE a.inserttime > '2019-05-05') tmp 
LEFT JOIN customer4 d ON tmp.num = d.num4 ;

答

具体的自己写两个sql语句对比下，哪条sql执行时间短用那条。直接告诉你，用on，这个设计到SQL执行顺序的问题，原因很简历，on比where的优先执行顺序更高。

答

可以这么写：
SELECT * from 
(SELECT a.inserttime, a.Num 
FROM contract a 
LEFT JOIN customer b ON a.Num = b.Num2
WHERE a.inserttime > '2019-07-28') tmp 
LEFT JOIN customer4 d ON tmp.num = d.num4 ;

答

直接在最内层的查询语句里新增一个条件即可a.inserttime > '2019-5-5'，如下
SELECT * from
(SELECT a.inserttime, a.Num
FROM test.contract a
LEFT JOIN test.customer b ON a.Num = b.Num2
where a.inserttime > '2019-5-5') tmp
LEFT JOIN test.customer4 d ON tmp.num = d.num4 ;

sql语句中Left join的on和where

相关推荐