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.

score 31 · Accepted Answer · answered Jun 03 '13 at 12:20

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 03 '13 at 12:20

Lasse V. Karlsen

380,855
102
628
825

thanks a lot Lasse! worked perfectly! thanks everyone who helped. :) – user2447740 Jun 03 '13 at 12:58

rghome · Answer 2 · 2018-06-27T09:25:11.253

10

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 '18 at 09:25

answered Oct 21 '15 at 13:45

rghome

8,529
8
43
62

score 3 · Answer 3 · edited Sep 15 '14 at 12:22

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 '14 at 12:22

user2863020

3
2

answered Jun 03 '13 at 12:29

Stef Heyenrath

9,335
12
66
121

1

In Oracle NULL <=> NULL. So your first example is simply wrong. **[Demo](http://rextester.com/QBUN21261)** – Lukasz Szozda Dec 03 '17 at 08:51

drjumper · Answer 4 · 2017-01-09T18:14:25.357

3

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

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

edited Jan 09 '17 at 18:14

answered Jan 03 '17 at 09:33

drjumper

111
1
3

Do see the date before posting buddy..Also the answer is already shared. – Vimal Bhaskar Jan 03 '17 at 09:37
I agree, this is more concise although perhaps less clear – Edd Jun 22 '18 at 15:14

score 2 · Answer 5 · answered Jun 04 '13 at 18:12

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.

score 0 · Answer 6 · edited Jun 03 '13 at 12:41

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 03 '13 at 12:41

René Höhle

26,716
22
73
82

answered Jun 03 '13 at 12:25

karel

107
1
1
7

Kevin · Answer 7 · 2017-01-11T03:04:53.270

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 '17 at 03:04

answered Jan 10 '17 at 03:07

Kevin

1
2

2

Please [edit] the answer and add explanation of why this should work. – Tushar Jan 10 '17 at 03:10

score 0 · Answer 8 · answered Dec 03 '17 at 08:53

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).

Oracle/PL SQL/SQL null comparison on where clause

8 Answers8