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 [mailtogsql-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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
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"
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
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.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
"(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?
Michael Kleiser 29 June 2004 17:47:01 [ permanent link ]
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?>
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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Peter Eisentraut 29 June 2004 19:27:48 [ permanent link ]
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.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
"(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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
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.
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
If you would like to report an abuse of our service, such as a spam message, please . Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .
