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
