1

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
Improve this question

5 Answers 5

Reset to default
6 
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

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

Comments

1

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;
Improve this answer

1 Comment

Yea, that is fine but i want to learn the functionality of in clause and how it returns data when there are null values.
0

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.

Improve this answer

5 Comments

That is not specific to Oracle. This is how NULL is defined for a relational database.
and IN is not equivalent to =. It is equivalent to = ANY
if my query is SELECT * FROM Store_Information WHERE store_name IN ('San Diego','null') even in this case i get 0 rows. Why?
@user1751356 -- My guess would be that there are trailing spaces in your store_name. Does WHERE trim(store_name) IN ('San Diego') return anything?
@user1751356 in Oracle a in clause with a null always returns zero rows. See (just after the table) docs.oracle.com/cd/B19306_01/server.102/b14200/…
0

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
Improve this answer

3 Comments

if my query is SELECT * FROM scott.Store_Information WHERE store_name IN ('San Diego','null') even in this case i get 0 rows. Why?
because 'Null' is a string it's looking for a store name called 'null' you'd have to use an in and an OR to get the results you want... Where (store_name in ('San Diego') or store_name is null) The if you remove the ' arond the null you'll have invalid syntax
I don't think the first example will work because an empty string is NULL in Oracle
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.