List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:October 31 2006 2:54am
Subject:RE: finding NULL records
View as plain text  
[snip]
Alan Nilsson wrote:
> 
> On Oct 30, 2006, at 3:27 PM, Jay Blanchard wrote:
> 
>> [snip]
>> I am trying to find records where the value of a filed is NULL.  I
>> know that there are records that have null values but the result is
>> always an empty set.
>>
>> eg:
>> select test_id from tests where test_id=NULL
>>
>> always returns an empty set when there are in fact records that have
>> a null value for test_id.  Is there some trick to finding null valued
>> records in MySQL?  This same sql has always worked on any other dbms
>> I have used.
>> [/snip]
>>
>> Of course this will return an empty set because you have only
selected
>> the test_id, try this;
>>
>> SELECT * FROM tests WHERE test_id IS NULL
> 
> Yes, that works, but I was also trying SELECT * instead of just the
key 
> field (just a typo in the example).  The problem was in the equal sign

> versus the 'IS' operator.  Any reason why MySQL does not honor 
> <field>=NULL?  Seems kind of odd.

Sql standard says you use IS NULL.
[/snip]

And the reason for that is because nothing is actually equal to NULL.
For a field to be equal to NULL it would actually have to contain NULL,
in which case it would not be actually NULL.
Thread
finding NULL recordsAlan Nilsson31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsAlan Nilsson31 Oct
    • Re: finding NULL recordsChris31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • RE: finding NULL recordsJerry Schwartz31 Oct
    • Re: finding NULL recordsMartijn Tonies31 Oct
      • Re: finding NULL recordsmizioumt15 Nov
        • RE: finding NULL recordsJerry Schwartz15 Nov
          • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsmizioumt17 Nov
          • Re: finding NULL recordsmizioumt17 Nov