Re: null vs empty string

Kent Thomas wrote:
> I have the following query:
>
> SELECT * FROM "sales_projects" WHERE (((sales_projects.prospect ILIKE E'%rancho murieta%') OR (sales_projects.prospect_type ILIKE E'%rancho murieta%') OR (sales_projects.application ILIKE E'%rancho murieta%') OR (sales_projects.project ILIKE E'%rancho murieta%') OR (sales_projects.city ILIKE E'%rancho murieta%') OR (sales_projects.state ILIKE 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.status IN (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, the second 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?

Others have already explained this; but they did not mention coalecse()
which might be useful for you.

> Secondly, can you offer some advice to EXCLUDE records where sales_projects.project_status is Ordered and Dead?

You already do this with

(sales_projects.project_status != E'Dead') AND
(sales_projects.project_status != E'Ordered')

As a general rule:

"(NOT A) AND (NOT B)" is the same as "NOT (A OR B)"

So you could also write:

NOT ( (sales_projects.project_status = E'Dead') OR
(sales_projects.project_status != E'Ordered')

If you wish to avoid some of the "ugly", then try:

sales_projects.project_status NOT IN (E'Dead',E'Ordered')

Of course, all these examples will still EXCLUDE the NULL values; so
perhaps you want this:

coalesce(sales_projects.project_status,'') NOT IN (E'Dead',E'Ordered')