Обсуждение: null vs empty string
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. Theonly 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.
The big thing to keep in mind when working with nulls is that NULL does not mean that there is nothing in the field. NULL means that the database does not know what is in the field. So, the only way to base a selection on whether or not a field is NULL is to use the ISNULL() function. Also, no comparison with NULL will ever return true. A query like "SELECT * FROM SomeTable WHERE NULL = NULL" will return no records. Since the database does not know what is in a NULL-valued field, it cannot be confident that one such field is equal to another, and since it's not confident, it has to return false. RobR
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. Theonly 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
Kent Thomas 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. Theonly 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? 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')
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!
On 1 July 2010 04:59, Mick <mickg01@verizon.net> wrote: > 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_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? >>> 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! > Although, to confuse things further, this is a great read: http://thoughts.j-davis.com/2009/08/02/what-is-the-deal-with-nulls/ Thom
On 2 July 2010 00:58, Bastiaan Olij <lists@basenlily.nl> wrote: > Hi all, > > Just to confuse matters more, this is how I try to think of NULL. > > I have a field called ANSWER_TO_QUESTION. > > What is the difference between this field being NULL, and this field > being empty? > > NULL means the question hasn't been answered yet, thus there is no > answer, there may be an answer once it is provided so you can't really > say the answer is empty. Empty would mean the answer has been given and > the answer is empty. > > Comparing something that doesn't exist is there for an impossibility. > > The debate becomes interesting when you look at for instance a table > with info about persons. You would store a first name, a middle name, > and a last name. Many people do not have a middle name. Should this > field then be NULL or should it be empty? > > Following the statement above this value should be: > NULL if you do not know the middle name of the person (the question has > not been answered yet, the person may actually have a middle name) > empty if that person doesn't have a middle name, the question has been > answered and the answer is an empty string > > When concatenating the string to get the full name of the person, > because it is NULL, you can't do a concatenation with it. Since you do > not know the middle name, you can't know the full name. Equally so, if > you want to find all people who do not have a middle name, you don't > want this person to come up, yes the middle name isn't given, but it is > also not given as empty. That person may indeed have a middle name. > > Whether making your middle name column nullable makes sense is a totally > different discussion. > For this particular field I would say a nullable middle name is > completely impractical. Yes you may not know the middle name of a person > so in theory you should save the middle name as NULL, but it really only > gets in your way. I would just simply say that if you don't know the > middle name, you don't care, and an empty string makes your life so much > easier. If you somehow want to flag that the middle name is unknown > because you want someone to come in after you and complete the data, > flag it in some other way that doesn't mean you have to make exceptions > everywhere in your queries to check for NULL. > > But say the field was in a table containing answers to a question sheet, > I definitely want to make a difference between questions that haven't > been answered, and questions to which the answer was an empty string (or > for a numeric, a difference between not having answered and the answer > being 0). > > One last one to trick the minds on the person example, birthday:) > NULL means: I don't know the persons birthday, so I don't know the > persons age, I can't calculate the age, I can't select the person based > on his/her date of birth > empty (does postgres even support this?) means: the person hasn't been > born yet, their age is 0, they definately stand appart from people who > have been born but who's date of birth is unknown. > Not something many would run into, but if you write software for a > maternity ward it could come in very handy to see the difference in these... > > Cheers, > > Bas > > Your message only went to me, so thought I'd share it with the list. (see above) Thom
Thanks Thom, Wondered why it hadn't posted to the list:) > Your message only went to me, so thought I'd share it with the list. (see above) > > Thom > > Cheers, Bas