3

Hi I am facing an issue regarding the NULL condition checking in sql query.

Following image is about selecting row for the table session_testset which has column "sync" with NULL Value.

It has one row with NULL value still it is returning 0 record.

Not returning row

And When I change the condition and check it for "NOT NULL". It is returning record.

returning row

But It is wrong. Why is it happening?

And this is my table structure
enter image description here

KDeogharkar
  • 10,939
  • 7
  • 51
  • 95
  • Is there *only 1 row*? Or is it only a *particular row* that is affected? –  Jan 02 '13 at 04:36
  • Those screen shots are indeed incorrect. Try it with the SQLite CLI, perhaps there is an issue with the program used. –  Jan 02 '13 at 04:39
  • can you post the insert statement you used to create that row, if available? – Matt Felzani Jan 02 '13 at 04:42
  • 1
    If the query `x = 'NULL'` works as expected - which is sounds like it does in the other comments - then the issues are two-fold 1) the value is not really `NULL` (but `'NULL'`) and 2) the program has a bug/mis-feature because it is highlighting `'NULL'` in red. –  Jan 02 '13 at 04:45
  • 1
    See http://stackoverflow.com/questions/7519621/where-is-null-not-working-in-sqlite – ErikE Jan 02 '13 at 04:48

3 Answers3

2

In my knowledge if we use the query like SELECT * FROM table_name WHERE column_name IS NULL returns the column where it has no values, that means unfilled.Your table has a value NULL.Check your code without placing NULL value in the column.

Levi
  • 2,103
  • 14
  • 9
Techy
  • 2,626
  • 7
  • 41
  • 88
2

I think Anaz has given you exactly what you need.
Having no value in a column is not the same as having a value 'NULL' in it when
you are trying to use IS NULL and IS NOT NULL.

I just tried a test query on a sample DB and these queries work just fine:

1. SELECT * FROM sample_db WHERE name IS NULL;  //returns row where name  
column has no value
2. SELECT * FROM sample_db WHERE name IS NOT NULL;  //returns row where name  
column has values
Rahul Bisht
  • 482
  • 3
  • 5
  • As far as the syntax forms allowed, [see my reply here](http://stackoverflow.com/questions/7519621/where-is-null-not-working-in-sqlite/7519682#7519682) –  Jan 02 '13 at 04:41
  • Seriously, the "NULL value" is different from the "no value NULL"? This is news to me! – ErikE Jan 02 '13 at 04:43
  • hey guys i tried SELECT * FROM session_testset WHERE sync = ''.. from what @Anaz is saying and it is worked :) – KDeogharkar Jan 02 '13 at 04:43
  • @BaZinga Interesting. File it as a bug with the program please. –  Jan 02 '13 at 04:44
  • @ErikE I think the poster was trying to say `'NULL'` is different than `NULL`. But it was lost to me as well. –  Jan 02 '13 at 04:45
  • Oh that is different @pst! – ErikE Jan 02 '13 at 04:51
  • The query that I am using now is working but i think it is not proper way to do. – KDeogharkar Jan 02 '13 at 05:06
0
select * from session_testset where  sync is null or sync = '';

or

select * from session_testset where ifnull(sync , '') = '';

or

select * from session_testset where coalesce(sync , '') = '';

or

select * from session_testset where ifnull(length(sync ), 0) = 0;
Nipun Jain
  • 626
  • 4
  • 6