Queries against NULL data

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Queries against NULL data

Kareem Badr
I read the section on the SQLite website regarding the inconsistencies
of how NULL values are handled in various databases. I didn't see
anything that mentioned what I am running into, though.

In a nutshell, the following queries do not return rows when test_field
is NULL. It seems counter-intuitive to me.

SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%'
SELECT * FROM my_table WHERE test_field != 'test value'

I can understand the NOT LIKE query not returning rows with NULL more
than I can understand the != query not returning any rows. NULL does not
equal 'test value'.

Is there an easy way around this, other than adding "OR test_field IS
NULL" to my queries? The example above is a lot simpler than the case I
need to handle in production, so I would like to avoid having to modify
every query to handle NULL values in a special case.
Reply | Threaded
Open this post in threaded view
|

Re: Queries against NULL data

Jay Sprenkle
On 6/1/06, Kareem Badr <[hidden email]> wrote:
> I read the section on the SQLite website regarding the inconsistencies
> of how NULL values are handled in various databases. I didn't see
> anything that mentioned what I am running into, though.
>
> In a nutshell, the following queries do not return rows when test_field
> is NULL. It seems counter-intuitive to me.
>
> SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%'
> SELECT * FROM my_table WHERE test_field != 'test value'

NULL is  "nothing was ever entered into this column"
Which is different from an "Empty string" was entered into this column.

A user might legitimately want a field left blank,
which is different from the case where they never entered anything for
that field.


>
> Is there an easy way around this, other than adding "OR test_field IS
> NULL" to my queries? The example above is a lot simpler than the case I
> need to handle in production, so I would like to avoid having to modify
> every query to handle NULL values in a special case.

No, except you could set a default value of the empty
string for that column. If no value is specified, then the default is used,
and you won't get NULLs.


--
SqliteImporter, SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite
Reply | Threaded
Open this post in threaded view
|

Re: Queries against NULL data

Clay Dowling
In reply to this post by Kareem Badr
The behavior you are seeing is what is expected.  NULL only every matches
the IS NULL criteria.  It doesn't pass equality, inequality, greater than
or less than tests.

The best option is to define the field as NOT NULL.  NULLs cause all
manner of trouble, and the best option is usually to avoid dealing with
them.

Clay Dowling


Kareem Badr said:

> I read the section on the SQLite website regarding the inconsistencies
> of how NULL values are handled in various databases. I didn't see
> anything that mentioned what I am running into, though.
>
> In a nutshell, the following queries do not return rows when test_field
> is NULL. It seems counter-intuitive to me.
>
> SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%'
> SELECT * FROM my_table WHERE test_field != 'test value'
>
> I can understand the NOT LIKE query not returning rows with NULL more
> than I can understand the != query not returning any rows. NULL does not
> equal 'test value'.
>
> Is there an easy way around this, other than adding "OR test_field IS
> NULL" to my queries? The example above is a lot simpler than the case I
> need to handle in production, so I would like to avoid having to modify
> every query to handle NULL values in a special case.
>


--
Simple Content Management
http://www.ceamus.com

Reply | Threaded
Open this post in threaded view
|

Re: Queries against NULL data

D. Richard Hipp
In reply to this post by Kareem Badr
Kareem Badr <[hidden email]> wrote:

> I read the section on the SQLite website regarding the inconsistencies
> of how NULL values are handled in various databases. I didn't see
> anything that mentioned what I am running into, though.
>
> In a nutshell, the following queries do not return rows when test_field
> is NULL. It seems counter-intuitive to me.
>
> SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%'
> SELECT * FROM my_table WHERE test_field != 'test value'
>
> I can understand the NOT LIKE query not returning rows with NULL more
> than I can understand the != query not returning any rows. NULL does not
> equal 'test value'.
>
> Is there an easy way around this, other than adding "OR test_field IS
> NULL" to my queries? The example above is a lot simpler than the case I
> need to handle in production, so I would like to avoid having to modify
> every query to handle NULL values in a special case.
>

I usually use the coalesce() function to fix this.  For
example:

   SELECT * FROm table WHERE coalesce(test_field,'') != 'test value';

The "coalesce(test_field,'')" will convert all NULL values of
test_field to an empty string and leave all other values unchanged.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Queries against NULL data

Kareem Badr
That seems to work perfectly.

How is the performance, comapared to changing the queries to check for
NULL explicitly?
> I usually use the coalesce() function to fix this.  For
> example:
>
>    SELECT * FROm table WHERE coalesce(test_field,'') != 'test value';
>
> The "coalesce(test_field,'')" will convert all NULL values of
> test_field to an empty string and leave all other values unchanged.
>  
Reply | Threaded
Open this post in threaded view
|

How do you unsubscribe?

Richard Battagline
How do you unsubscribe?

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 
Reply | Threaded
Open this post in threaded view
|

Re: How do you unsubscribe?

Kurt Welgehausen
Richard Battagline <[hidden email]> wrote:

> How do you unsubscribe?

Read the headers of any message from the group.
Reply | Threaded
Open this post in threaded view
|

Re: How do you unsubscribe?

Nemanja Corlija
In reply to this post by Richard Battagline
On 6/1/06, Richard Battagline <[hidden email]> wrote:
> How do you unsubscribe?

Send an email to [hidden email]


--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: How do you unsubscribe?

Eugene Wee
In reply to this post by Richard Battagline
Hi Richard,

Send an email to [hidden email]

Actually, I sent an email to:
sqlite-users-unsubscribe-battagline_rf=[hidden email]

Hopefully it has resulted in an unsubscribe confirmation message sent to you,
and replying to that email should unsubscribe you from this list.

Regards,
Eugene Wee

Richard Battagline wrote:
> How do you unsubscribe?
Reply | Threaded
Open this post in threaded view
|

Re: How do you unsubscribe?

G. Roderick Singleton
In reply to this post by Richard Battagline
On Thu, 2006-06-01 at 09:28 -0700, Richard Battagline wrote:
> How do you unsubscribe?
>

Send an email to [hidden email] which will return all the
information about the list or check your sqlite mailbox for the welcome
message.
--
G. Roderick Singleton <[hidden email]>
PATH tech

smime.p7s (2K) Download Attachment