List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 6 2004 2:41am
Subject:RE: IS NULL works, but IN (NULL) doesn't
View as plain text  
At 18:50 -0700 6/5/04, Daevid Vincent wrote:
>  > If you need it to work, I'm afraid you're out of luck.  Think about
>>  what a IN (x,y,z) means.  It's basically the same as a = x or a = y
>>  or a = z.  So wifi_list IN (NULL) is like wifi_list = NULL,
>>  which is never true.
>
>Thanks for the reply Paul, and I get that NULL is a special case value, but
>if "wifi_list IS NULL" works, then why doesn't "wifi_list = NULL"?

Because that is not now NULL comparisons work.  The expression
any_value = NULL is *never* true.

There is a special MySQL-Specific <=> operator that is like =
but evaluates to true for any_value <=> NULL when any_value is NULL.
But that's not going to make any_value IN (NULL) work, which is what
you appear to want.

>
>Logically they seem to be the same, is "IS" only used for NULL values or
>something? Or in other words, it would make sense to me that I should be
>able to say "wifi_list IS 3" instead of an = sign.

Yes, IS is only for IS NULL comparisons.

You might want to take a look at this section:

http://dev.mysql.com/doc/mysql/en/Problems_with_NULL.html

>
>>  -----Original Message-----
>>  From: Paul DuBois [mailto:paul@stripped]
>>  Sent: Thursday, June 03, 2004 7:56 PM
>>
>>  At 18:32 -0700 6/3/04, Daevid Vincent wrote:
>>  >How come this one works:
>>  >
>>  >SELECT wifi_list,	IFNULL(wifi_list, 0) as wifi_list_new,
>>  >FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id =
>>  >wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac
>>  >WHERE last_seen >= CURRENT_DATE AND wifi_list IS NULL;
>>  >
>>  >This one works too
>>  >
>>  >SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new,
>>  >FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id =
>>  >wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac
>>  >WHERE last_seen >= CURRENT_DATE HAVING wifi_list_new IN (0,1,2);
>>  >
>>  >But this one doesn't... I *need* this to work:
>>
>>  >SELECT wifi_list, IFNULL(wifi_list, 0) as wifi_list_new,
>>  >FROM wifi_table LEFT JOIN Swordfish.scanner ON scanner.scanner_id =
>>  >wifi_table.scanner_id LEFT JOIN wifi_wlist_table ON wifi_table_mac = mac
>  > >WHERE last_seen >= CURRENT_DATE AND wifi_list IN (NULL);


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
IS NULL works, but IN (NULL) doesn'tDaevid Vincent4 Jun
  • Re: IS NULL works, but IN (NULL) doesn'tPaul DuBois4 Jun
    • RE: IS NULL works, but IN (NULL) doesn'tDaevid Vincent6 Jun
      • RE: IS NULL works, but IN (NULL) doesn'tPaul DuBois6 Jun