CODEWITHSUNDEEP
sqloracle-databaseplsqlnull
user2447740
user2447740

Reputation: 273

Oracle/PL SQL/SQL null comparison on where clause

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.

Upvotes: 25

Views: 56511

Answers (8)

rghome
rghome

Reputation: 8819

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

Upvotes: 13

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

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

Upvotes: 0

Kevin
Kevin

Reputation: 1

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'

Upvotes: 0

drjumper
drjumper

Reputation: 111

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

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

Upvotes: 3

Stef Heyenrath
Stef Heyenrath

Reputation: 9820

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.

Upvotes: 3

JOATMON
JOATMON

Reputation: 109

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.

Upvotes: 2

karel
karel

Reputation: 107

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

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391326

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)

Upvotes: 31

Related Questions