Skip to Main Content
  • Questions
  • using of binding variable in ( IN clause ) with null condition check

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Niraj.

Asked: December 09, 2020 - 6:56 pm UTC

Last updated: December 11, 2020 - 5:39 pm UTC

Version: Oracle18c

Viewed 1000+ times

You Asked

Hi Tom,

I am struggling with a simple but tricky logic during SQL.

Requirement :- I want to call SQL from shell script passing character variable as input which could be null also

my sql :- select * from scott.emp where ename in (&1)
&1 possible values :- 1. 'JOHN'
                      2. 'JOHN','ROCKY'
                      3. Null ( NOthing to enter on user input) 


while input values 1 and 2 are working fine, when NULL is entered , it should not check any where condition and should display all records from emp table i.e. 1=1

I am facing problem with null handling in "IN" clause and bypassing the where clause.

Please help ....

and Chris said...

To return rows when the input is empty, you also need to check if it IS NULL:

create table t (
  c1 int
);

insert into t values ( 1 );

set define on
define v

old:select * from t
where  c1 in ( &v ) or &v is null
new:select * from t
where  c1 in ( null ) or null is null
C1   
    1 

select * from t
where  c1 in ( &v ) or &v is null;


If you're coding this in an application (not a SQL*Plus/SQLcl script), you should use bind variables. Doing a variable IN list with these is trickier; for solutions see:

https://asktom.oracle.com/pls/apex/asktom.search?tag=how-can-i-do-a-variable-in-list-200005

You'll still need the IS NULL check when using binds.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.