Hej guys, could someone explain to me difference between NVL() and OR in below query :
Select count(*) from SHIPMENTSTATUS WHERE insert_date between trunc(sysdate) -2 and trunc(sysdate) -1 or update_date BETWEEN trunc(sysdate) -2 and trunc(sysdate) -1
Number of rows: 44937
and
Select count(*) from SHIPMENTSTATUS where NVL(UPDATE_DATE, INSERT_DATE) between trunc(sysdate) -2 and trunc(sysdate) -1
Number of rows: 44782
Why we have this difference: 155 rows? i built a few query based on nvl and notice that issue and going to switch to OR
, but I really want to know, why it such a difference. Thank you for explanation
Advertisement
Answer
When UPDATE_DATE
is not null NVL(UDATE_DATE, INSERT_DATE)
evalulates to UPDATE_DATE
and INSERT_DATE
will not be used at all. Records with INSERT_DATE
in the range, but a non null UPDATE_DATE
outside of that range will be included in the first query, but excluded in the second.