RE: IS NULL works, but IN (NULL) doesn't
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:[EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IS NULL works, but IN (NULL) doesn't
> 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"? 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. > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED] > 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); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL works, but IN (NULL) doesn't
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: 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. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
