CODEWITHSUNDEEP
sqloracle-database
user1751356
user1751356

Reputation: 615

Oracle null values

Can somebody please explain the functionality of the below query in oracle db and why is it not returning the last null row. And also please explain me not in functionality in case of null values. 

Table Store_Information
store_name    Sales  Date
Los Angeles   $1500  Jan-05-1999
San Diego      $250  Jan-07-1999
San Francisco  $300  Jan-08-1999
Boston         $700  Jan-08-1999
(null)         $600  Jan-10-1999


SELECT *
FROM scott.Store_Information
WHERE store_name IN (null)

STORE_NAME           SALES                DATE                      
-------------------- -------------------- ------------------------- 

0 rows selected

Upvotes: 1

Views: 378

Answers (5)

Jean-Christophe Blanchard
Jean-Christophe Blanchard

Reputation: 438

If you want your query select field with null value you can : solution 1 : use where ... IS NULL ...

solution 2 : or if you want absolutely use a IN you can use a NVL eg :

SELECT * FROM scott.Store_Information WHERE NVL(store_name, 'NULL_STORE_NAME') IN ('NULL_STORE_NAME')

but in the second case you make the assumption that you can't have a store name named 'NULL_STORE_NAME'... so usually it's better to use solution 1 in my opinion...

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Null is a special value which doesn't follow normal conventions of string or numeric comparison. Evaluating null using these common methods will always evaluate to FALSE unless you use some of the special built in functions.

Select * from Store_Information
where nvl(store_name,'') in ('')

Select * from store_information
where coalesce(store_name, 'Missing') in ('Missing')

Select * from store_information 
where store_name is null

Upvotes: 0

Nivas
Nivas

Reputation: 18344

Oracle NULLS are special:

nothing is equal to null
nothing is NOT equal to null

Since in is equivalent to a = any, this applies to in also.

So, you cannot use NULL in an in or not in clause and expect proper results.

Upvotes: 0

gpojd
gpojd

Reputation: 23055

If the value you are looking for is a null value, the query should be: 

SELECT *
FROM scott.Store_Information
WHERE store_name IS NULL;

Upvotes: 1

user330315
user330315

Reputation: 

SELECT *
FROM scott.Store_Information
WHERE store_name IS null;

NULL can not be "compared" as other (real) values. Therefor you have to use IS NULL or IS NOT NULL.

Here is a series of blog posts regarding this topic: http://momjian.us/main/blogs/pgblog/2012.html#December_26_2012

Upvotes: 6

Related Questions