Re: null vs empty string

On 06/30/2010 08:41 AM, Thom Brown wrote:
> On 30 June 2010 13:21, Kent Thomas<kent@solarbee.com>  wrote:
>> I have the following query:
>>
>> SELECT * FROM "sales_projects" WHERE (((sales_projects.prospect ILIKE E'%rancho murieta%') OR
(sales_projects.prospect_typeILIKE E'%rancho murieta%') OR (sales_projects.application ILIKE E'%rancho murieta%') OR
(sales_projects.projectILIKE E'%rancho murieta%') OR (sales_projects.city ILIKE E'%rancho murieta%') OR
(sales_projects.stateILIKE E'%rancho murieta%') OR (sales_projects.project_status ILIKE E'%rancho murieta%')) AND
(((sales_projects.project_status!= E'Dead') AND (sales_projects.project_status != E'Ordered')) AND
((sales_projects.statusIN (E'Active',E'Expired')) AND (sales_projects.kind = E'Project')))) 
>>
>> Yes, it is ugly, but that's not the issue.  This query returns just one record when I would expect it to return two.
The only difference in the two records is in the sales_projects.project_status field.  One record has an empty string,
thesecond has a null value.  The NULL value in sales_projects.project_status is not returned. 
>>
>> Can someone explain why the NULL value in sales_projects.project_status field does not fit this query?
>> Secondly, can you offer some advice to EXCLUDE records where sales_projects.project_status is Ordered and Dead?
>>
>> Thanks a million for any help.
> NULL values won't be returned if you're matching against a value, or
> excluding specific values from the result because NULL can't be
> compared with non-nulls.
>
> If sale_projects.project_status has a NULL value, checking to see
> whether it's not equal to a value won't return it because it isn't
> known.  You would have to use "OR IS NULL" in where appropriate.
>
> An analogy would be having 3 boxes.  1 has an orange in with the lid
> off, 1 with an apple with the lid off, and 1 with the lid on.  You
> can't say either match the contents of the 3rd box because you don't
> know what's in it.
>
> Regards
>
> Thom
>
Schroedinger's cat!