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'吗?

6个回答

以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 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的优先执行顺序更高。

直接在最内层的查询语句里新增一个条件即可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 ;

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问