Oracle SQL ignores condition in WHERE clause

I have this simple part of SQL query

where aia.invoice_num in (:invoice_num) or aia.invoice_num is null
and ipa.payment_date between (:start_date) and (:end_date) or ipa.payment_date >= (:start_date) or (:start_date is null and :end_date is null)
and ipa.PAYEE_NAME in (:payee) or :payee is null

I have 4 parameters, invoice_num, start_date, end_date and Payee, I want it to be just like a filter, If one of the conditions above is present then it must be applied, if not present then it must be ignored, but it just doesn’t work as expected, if I pass invoice_num the result will be all the invoices anyway whether other parameters are present or null, how can I force every condition if present and make it ignored if null ?

Answer

Your query does not work because:

1. AND has a higher operator precedence than OR which means it is evaluated as:

   where ( aia.invoice_num in (:invoice_num) )
   or    (     aia.invoice_num is null
           and ipa.payment_date between (:start_date) and (:end_date) )
   or    ( ipa.payment_date >= (:start_date) )
   or    ( :start_date is null
           and :end_date is null
           and ipa.PAYEE_NAME in (:payee) )
   or    ( :payee is null )
   

   so if any of the OR conditions is true then the rest can be ignored.

2. You compare aia.invoice_num is null rather than :invoice_num IS NULL.

To fix it, you can use:

WHERE ( aia.invoice_num = :invoice_num  OR :invoice_num IS NULL )
AND   ( ipa.payment_date >= :start_date OR :start_date  IS NULL )
AND   ( ipa.payment_date <= :end_date   OR :end_date    IS NULL )
AND   ( ipa.PAYEE_NAME   = :payee       OR :payee       IS NULL )