Can I use NULL or NOT NULL in an Oracle SQL parameter?

Question

Let's say I have two Oracle SQL queries that I'm using in various places in my code:

select *
from employees
where manager_id IS NULL
and etc etc etc...

and

select *
from employees
where manager_id IS NOT NULL
and etc etc etc...

The only difference between the two queries is that manager_id is NULL in the first one, and NOT NULL in the second one.

Is there any way to write this as one generic statement? Is it possible to pass in NULL or NOT NULL in a parameter? Something like this:

select *
from employees
where manager_id = ?
and etc etc etc...

I know the above example doesn't work (and I know why). My question is more along the lines of is there a more elegant solution than managing two separate SQL strings that are 99% similar?

@DmitryAgibov Sorry, but this is not a good idea - it would make manager_id non-sargable. Also, the database would need to defend itself from ever getting this "special" value (e.g. through a CHECK). — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Sep 10, 2018 at 11:49

Gordon Linoff · Accepted Answer · 2018-09-10 11:28:17Z

8

You would need to use more complex logic. Perhaps:

where ( (? = 'not null' and manager_id is not null) or
        (? = 'null' and manager_id is null)
      )

answered Sep 10, 2018 at 11:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Migs Over a year ago

is there a specific term for this approach?

Collectives™ on Stack Overflow

Can I use NULL or NOT NULL in an Oracle SQL parameter?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related