Thread: FW: "=" operator vs. "IS"
You are exactly right - the way I think about it is that if you have two values which are unknown (a null column and NULL) it does not follow that they are equal to each other. As far as TRUE and FALSE go, from what I know you can use = to compare them with boolean columns, unless I misunderstood your question. Dmitri -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Stefan Weiss Sent: Monday, June 28, 2004 6:02 PM To: pgsql-sql@postgresql.org Subject: [SQL] "=" operator vs. "IS" Hi. I'm just curious - why is it not possible to use the "=" operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like "AND foo.bar = NULL". Is it because NULL does not "equal" any value, and the expression should be read as "foo.bar is unknown"? Or is there something else I'm missing? If it's the "unknown" part, then why can't I use "=" to compare with TRUE or FALSE? cheers, stefan ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Re, thanks for all the replies. On Tuesday, 29 June 2004 00:17, Dmitri Bichko wrote: > As far as TRUE and FALSE go, from what I know you can use = to compare > them with boolean columns, unless I misunderstood your question. Sorry, I must have remembered that incorrectly, or maybe I've been thinking of a different DBMS or version. "table.col = TRUE" seems to work fine for me now. I understand that the result of "(anything) = NULL" will always NULL, so it's a waste of breath. That's where the "IS" operator(?) comes in, which allows for comparison with NULL. The only question left is why "IS" can also be used to compare with the TRUE/FALSE keywords (when a simple "=" should be sufficient here), but not to compare two boolean columns. cheers, stefan BTW, I really liked Mike's explanation that "it just IS" ;-)
On Tue, 29 Jun 2004, Stefan Weiss wrote: > On Tuesday, 29 June 2004 00:17, Dmitri Bichko wrote: > > As far as TRUE and FALSE go, from what I know you can use = to compare > > them with boolean columns, unless I misunderstood your question. > > Sorry, I must have remembered that incorrectly, or maybe I've been > thinking of a different DBMS or version. "table.col = TRUE" seems to > work fine for me now. > > I understand that the result of "(anything) = NULL" will always NULL, > so it's a waste of breath. That's where the "IS" operator(?) comes in, > which allows for comparison with NULL. The only question left is why > "IS" can also be used to compare with the TRUE/FALSE keywords (when a > simple "=" should be sufficient here), but not to compare two boolean > columns. IS TRUE and IS FALSE have a different effect from =true and =false when the left hand side is NULL. The former will return false, the latter will return NULL.
My question is why is the form "(anything) = NULL" allowed? Since "(anything) = NULL" is always Null, this cannot be what the coder intended. This is much different when comparing two variables, where the coder may have to handle the cases where the variables areNull. Here the comparison is to a constant. Rick
Rich Hall <rhall@micropat.com> writes:
> My question is why is the form
> "(anything) = NULL"
> allowed?
If you think it shouldn't be, you can turn on the transform_null_equals
flag. However, past experience has shown that that breaks more things
than it fixes. In any case, few people like to depend on such a
thoroughly nonstandard behavior ...
regards, tom lane
NULL is handled like "unknow" When you comparing something with an unknown value, you are not able to say if they are equal or not. So the result is also unknown. The result NULL is correct. If you whant to check if somethings is NULL you have to use "(anything) IS NULL" Rich Hall schrieb: > My question is why is the form > > "(anything) = NULL" > > allowed? > > Since > > "(anything) = NULL" is always Null, this cannot be what the coder > intended. > > This is much different when comparing two variables, where the coder may > have to handle the cases where the variables are Null. Here the > comparison is to a constant. > > > > > Rick > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Rich Hall wrote: > My question is why is the form > > "(anything) = NULL" > > allowed? > > Since > > "(anything) = NULL" is always Null, this cannot be what the coder > intended. Using that same line of argument, why is 1+1 allowed? The coder clearly knows that it is 2, so why is he writing that? Many queries are generated by automatic tools that definitely intend what they say.
On Tue, 29 Jun 2004, Greg Stark wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > > IS TRUE and IS FALSE have a different effect from =true and =false when > > the left hand side is NULL. The former will return false, the latter will > > return NULL. > > No, actually they both return false. For purposes of anyone reading this in the archives I'd meant NULL IS TRUE and NULL IS FALSE return false vs NULL=true and NULL=false returning NULL.
Rich Hall <rhall@micropat.com> writes: > "(anything) = NULL" is always Null, this cannot be what the coder intended. I often have such things in my SQL. Consider what happens when you have SQL constructed dynamically. Or more frequently, consider that many drivers still don't use the new binary placeholder syntax and emulate it by putting the parameters directly into the SQL. -- greg
Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > IS TRUE and IS FALSE have a different effect from =true and =false when > the left hand side is NULL. The former will return false, the latter will > return NULL. No, actually they both return false. (But thanks, I didn't even realize they were special this way) -- greg
> I'm just curious - why is it not possible to use the "=" operator to > compare values with NULL? I suspect that the SQL standard specified it > that way, but I can't see any ambiguity in an expression like "AND > foo.bar = NULL". Is it because NULL does not "equal" any value, and the > expression should be read as "foo.bar is unknown"? Or is there something > else I'm missing? As noted elsewhere, joining two tables on "a.foo = b.foo" where both foo values are NULL is not usually what you want. But if you really, truly do want that, then you always have this: coalesce(a.foo,0) = coalesce(b.foo,0) or a similar construct, using something in place of zero that has the same base type as a.foo and b.foo, and which doesn't occur in either table. (Why? Because you really don't want a.foo = coalesce(b.foo,0) or b.foo = coalesce(a.foo,0) to give you false positives.) -- (Posted from an account used as a SPAM dump. If you really want to get in touch with me, dump the 'jboes' and substitute 'mur'.) ________ Jeffery Boes <>< jboes@qtm.net