CODEWITHSUNDEEP
sqloracle-databaseselectnull
JoshKni8
JoshKni8

Reputation: 155

Oracle Selecting Columns with IN clause which includes NULL values

So I am comparing two Oracle databases by grabbing random rows in database A, and searching for these rows in database B based off their key columns. Then I compare the rows which are returned in java.

I am using the following query to find rows in database B using the key columns from database A:

select * from mytable 
Where (Key_Column_A,Key_Column_B,Key_Column_C) 
in (('1','A', 'cat'),('2','B', 'dog'),('3','C', ''));

This works just fine for the first two sets of keys, but the third key('3','C', '') does not work because there is a null value in the third column. Changing the statement to ('3','C', NULL) or changing the SQL to 

select * from mytable 
Where (Key_Column_A,Key_Column_B,Key_Column_C) 
in ((('1','A', 'cat'),('2','B', 'dog'),('3','C', ''))
OR (Key_Column_A,Key_Column_B,Key_Column_C) IS NULL);

will not work either.

Is there a way to include a null column in an IN clause? And if not, is there a way to efficiently do the same thing? (My only solution currently is to create a check to make sure there are no nullable columns in my keys which would make this process rather unefficient and somewhat messy).

Upvotes: 1

Views: 701

Answers (1)

Fnaxiom
Fnaxiom

Reputation: 396

You can use it this way. I think it would work. 

 select * from mytable 
  Where (NVL(Key_Column_A,''),NVL(Key_Column_B,''),NVL(Key_Column_C,'')) 
   in (('1','A', 'cat'),('2','B', 'dog'),('3','C', ''));

I am not sure about this (Key_Column_A,Key_Column_B,Key_Column_C) IS NULL. Wouldn't this imply that all of the columns (A,B,C) are NULL ?

Upvotes: 2

Related Questions