Skip to content
Advertisement

Difference between NVL and OR in SQL oracle

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement