Oracle/PL SQL/SQL null comparison on where clause

Question

Just a question about dealing will null values in a query.

For example I have the following table with the following fields and values

TABLEX
Column1
1        
2        
3      
4        
5      
---------
Column2
null
A
B
C
null

I'm passing a variableY on a specific procedure. Inside the procedure is a cursor like this

CURSOR c_results IS
SELECT * FROM TABLEX where column2 = variableY

now the problem is variableY can be either null, A, B or C if the variableY is null i want to select all record where column2 is null, else where column2 is either A, B or C.

I cannot do the above cursor/query because if variableY is null it won't work because the comparison should be

CURSOR c_results IS
SELECT * FROM TABLEX where column2 IS NULL

What cursor/query should I use that will accomodate either null or string variable.

Sorry if my question is a bit confusing. I'm not that good in explaining things. Thanks in advance.

Lasse V. Karlsen · Accepted Answer · 2013-06-03 12:20:10Z

31

Either produce different SQL depending on the contents of that parameter, or alter your SQL like this:

WHERE (column2 = variableY) OR (variableY IS NULL AND column2 IS NULL)

answered Jun 3, 2013 at 12:20

Lasse V. Karlsen

394k107 gold badges651 silver badges845 bronze badges

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

1 Comment

user2447740 Over a year ago

thanks a lot Lasse! worked perfectly! thanks everyone who helped. :)

rghome · Accepted Answer · 2018-06-27 09:25:11Z

14

Oracle's Ask Tom says:

where decode( col1, col2, 1, 0 ) = 0  -- finds differences

or

where decode( col1, col2, 1, 0 ) = 1  -- finds sameness - even if both NULL

Safely Comparing NULL Columns as Equal

edited Jun 27, 2018 at 9:25

answered Oct 21, 2015 at 13:45

rghome

8,9018 gold badges47 silver badges67 bronze badges

Comments

user2863020 · Accepted Answer · 2014-09-15 12:22:31Z

3

You could use something like:

SELECT * FROM TABLEX WHERE COALESCE(column2, '') = COALESCE(variableY, '')

(COALESCE takes the first non NULL value)

Note this will only work when you the column content cannot be '' (empty string). Else this statement will fail because NULL will match '' (empty string).

(edit) You could also consider:

SELECT * FROM TABLEX WHERE COALESCE(column2, 'a string that never occurs') = COALESCE(variableY, 'a string that never occurs')

This will fix the '' fail hypothesis.

edited Sep 15, 2014 at 12:22

user2863020

32 bronze badges

answered Jun 3, 2013 at 12:29

Stef Heyenrath

9,97014 gold badges73 silver badges132 bronze badges

1 Comment

Lukasz Szozda Over a year ago

In Oracle NULL <=> NULL. So your first example is simply wrong. Demo

drjumper · Accepted Answer · 2017-01-09 18:14:25Z

3

Below is similar to "top" answer but more concise:

WHERE ((column2 = variableY ) or COALESCE( column2, variableY) IS NULL)

edited Jan 9, 2017 at 18:14

answered Jan 3, 2017 at 9:33

drjumper

1211 silver badge3 bronze badges

2 Comments

Vimal Bhaskar Over a year ago

Do see the date before posting buddy..Also the answer is already shared.

Edd Over a year ago

I agree, this is more concise although perhaps less clear

JOATMON · Accepted Answer · 2013-06-04 18:12:46Z

May not be appropriate depending on the data you're looking at, but one trick I've seen (and used) is to compare NVL(fieldname,somenonexistentvalue).

For example, if AGE is an optional column, you could use:

if nvl(table1.AGE,-1) = nvl(table2.AGE,-1)

This relies on there being a value that you know will never be allowed. Age is a good example, salary, sequence numbers, and other numerics that can't be negative. Strings may be trickier of course - you may say that you'll never have anyone named 'xyzzymaryhadalittlelamb" or something like that, but the day you run with that assumption you KNOW they'll hire someone with that name!!

All that said: "where a = b or (a is null and b is null)" is the traditional way to solve it. Which is unfortunate, as even experienced programmers forget that part of it sometimes.

René Höhle · Accepted Answer · 2013-06-03 12:41:25Z

0

Try using the ISNULL() function. you can check if the variable is null and if so, set a default return value. camparing null to null is not really possible. remember: null <> null

edited Jun 3, 2013 at 12:41

René Höhle

27.4k22 gold badges78 silver badges91 bronze badges

answered Jun 3, 2013 at 12:25

karel

1071 gold badge1 silver badge7 bronze badges

Comments

Kevin · Accepted Answer · 2017-01-11 03:04:53Z

0

WHERE variableY is null or column2 = variableY 

for example:
create table t_abc (
id number(19) not null,
name varchar(20)
);

insert into t_abc(id, name) values (1, 'name');
insert into t_abc(id, name) values (2, null);
commit;

select * from t_abc where null is null or name = null;
--get all records

select * from t_abc where 'name' is null or name = 'name';
--get one record with name = 'name'

edited Jan 11, 2017 at 3:04

answered Jan 10, 2017 at 3:07

Kevin

12 bronze badges

1 Comment

Tushar Over a year ago

Please edit the answer and add explanation of why this should work.

Lukasz Szozda · Accepted Answer · 2017-12-03 08:53:37Z

0

You could use DUMP:

SELECT * 
FROM TABLEX 
WHERE DUMP(column2) = DUMP(variableY);

DBFiddle Demo

Warning: This is not SARG-able expression so there will be no index usage.

With this approach you don't need to search for value that won't exists in your data (like NVL/COALESCE).

answered Dec 3, 2017 at 8:53

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Oracle/PL SQL/SQL null comparison on where clause

8 Answers 8

1 Comment

Comments

1 Comment

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

Comments

1 Comment

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related