List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 2 2002 5:46am
Subject:Re: behaviour of WHERE statement with NULL fields
View as plain text  
In the last episode (Nov 02), Kevin McManus said:
> >Description:
>         The WHERE statement does not correctly return rows matching NULL
> 	fields when using NOT with IN, LIKE or REGEXP - or using REGEXP
> 	with negation ^

Please see http://www.mysql.com/doc/en/Working_with_NULL.html for a
description of how the SQL language treats NULLs.  MySQL is working as
designed.
 
> >Fix:
> 	Use IS NULL
>         SELECT * FROM bugtest WHERE grp IS NULL OR grp REGEXP '[^AB]'
> 	Returns what would be expected from the last How-To-Repeat example

Correct.  The only way to select based on a NULL in a field is with IS
NULL.

-- 
	Dan Nelson
	dnelson@stripped
Thread
behaviour of WHERE statement with NULL fieldsKevin McManus2 Nov
  • Re: behaviour of WHERE statement with NULL fieldsPaul DuBois2 Nov
  • Re: behaviour of WHERE statement with NULL fieldsDan Nelson2 Nov
Re: behaviour of WHERE statement with NULL fieldsMichael T. Babcock5 Nov