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.