pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"=" operator vs. "IS"
15+ messages / 12 participants
[nested] [flat]
* "=" operator vs. "IS"
@ 2004-06-28 22:02 Stefan Weiss <spaceman@foo.at>
2004-06-28 22:27 ` Re: "=" operator vs. "IS" Steve Crawford <scrawford@pinpointresearch.com>
2004-06-28 22:28 ` Re: "=" operator vs. "IS" Michael A Nachbaur <mike@nachbaur.com>
0 siblings, 2 replies; 15+ messages in thread
From: Stefan Weiss @ 2004-06-28 22:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "=" operator vs. "IS"
2004-06-28 22:02 "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
@ 2004-06-28 22:27 ` Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 15+ messages in thread
From: Steve Crawford @ 2004-06-28 22:27 UTC (permalink / raw)
To: Stefan Weiss <spaceman@foo.at>; pgsql-sql
> 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?
You've got it. NULL is the _absence_ of a known value so any
comparison or operation on it yields an unknown result.
So why can't you use = NULL?
Consider the a list of names and ages where Jack's and Jill's ages are
null. Now we run a query to list people who are of the same age.
Should Jack and Jill be listed as being the same age? Of course not.
You can't compare whether the two unknown values are equal any more
than you could determine whether or not they are over 18.
The SQL spec and PostgreSQL properly use and enforce this
interpretation of NULL.
The correct way to ask your questions is ...where foo.bar is null...
Cheers,
Steve
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "=" operator vs. "IS"
2004-06-28 22:02 "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
@ 2004-06-28 22:28 ` Michael A Nachbaur <mike@nachbaur.com>
2004-06-28 23:00 ` Re: "=" operator vs. "IS" terry@ashtonwoodshomes.com
1 sibling, 1 reply; 15+ messages in thread
From: Michael A Nachbaur @ 2004-06-28 22:28 UTC (permalink / raw)
To: Stefan Weiss <spaceman@foo.at>; +Cc: pgsql-sql
On June 28, 2004 03:02 pm, Stefan Weiss wrote:
> 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 far as I have been able to tell, it is one of those quirks about SQL that
you shouldn't bother trying to understand. It just IS. <rimshot/>
--
Michael A. Nachbaur <mike@nachbaur.com>
http://nachbaur.com/pgpkey.asc
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "=" operator vs. "IS"
2004-06-28 22:02 "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
2004-06-28 22:28 ` Re: "=" operator vs. "IS" Michael A Nachbaur <mike@nachbaur.com>
@ 2004-06-28 23:00 ` terry@ashtonwoodshomes.com
0 siblings, 0 replies; 15+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-06-28 23:00 UTC (permalink / raw)
To: 'Michael A Nachbaur' <mike@nachbaur.com>; 'Stefan Weiss' <spaceman@foo.at>; +Cc: pgsql-sql
Think about a join where you do something like:
t1.f1 = t2.f1
If both columns were blank, would you want the join to succeed? Probably not, if you did, then you
would potentially have a cartesian select. This is not a good explanation of NULL non-equality, but
I thought it might be useful.
NOTE: A related topic is OUTER JOIN's which is how the above join would properly be implemented
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Michael A Nachbaur
> Sent: Monday, June 28, 2004 6:28 PM
> To: Stefan Weiss
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] "=" operator vs. "IS"
>
>
> On June 28, 2004 03:02 pm, Stefan Weiss wrote:
> > 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 far as I have been able to tell, it is one of those quirks
> about SQL that
> you shouldn't bother trying to understand. It just IS. <rimshot/>
>
> --
> Michael A. Nachbaur <mike@nachbaur.com>
> http://nachbaur.com/pgpkey.asc
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* FW: "=" operator vs. "IS"
@ 2004-06-28 22:17 Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-28 23:02 ` Re: FW: "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
2004-06-29 14:28 ` Re: FW: "=" operator vs. "IS" Rich Hall <rhall@micropat.com>
2004-07-01 15:02 ` Re: FW: "=" operator vs. "IS" Jeff Boes <jboes@qtm.net>
0 siblings, 3 replies; 15+ messages in thread
From: Dmitri Bichko @ 2004-06-28 22:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
@ 2004-06-28 23:02 ` Stefan Weiss <spaceman@foo.at>
2004-06-28 23:24 ` Re: FW: "=" operator vs. "IS" Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 1 reply; 15+ messages in thread
From: Stefan Weiss @ 2004-06-28 23:02 UTC (permalink / raw)
To: pgsql-sql
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" ;-)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-28 23:02 ` Re: FW: "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
@ 2004-06-28 23:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-29 17:33 ` Re: FW: "=" operator vs. "IS" Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Stephan Szabo @ 2004-06-28 23:24 UTC (permalink / raw)
To: Stefan Weiss <spaceman@foo.at>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-28 23:02 ` Re: FW: "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
2004-06-28 23:24 ` Re: FW: "=" operator vs. "IS" Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-06-29 17:33 ` Greg Stark <gsstark@mit.edu>
2004-06-29 17:46 ` Re: FW: "=" operator vs. "IS" Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: Greg Stark @ 2004-06-29 17:33 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Stefan Weiss <spaceman@foo.at>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-28 23:02 ` Re: FW: "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
2004-06-28 23:24 ` Re: FW: "=" operator vs. "IS" Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-29 17:33 ` Re: FW: "=" operator vs. "IS" Greg Stark <gsstark@mit.edu>
@ 2004-06-29 17:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2004-06-29 17:46 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Stefan Weiss <spaceman@foo.at>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
@ 2004-06-29 14:28 ` Rich Hall <rhall@micropat.com>
2004-06-29 14:47 ` Re: FW: "=" operator vs. "IS" Michael Kleiser <mkl@webde-ag.de>
2004-06-29 14:57 ` Re: FW: "=" operator vs. "IS" Tom Lane <tgl@sss.pgh.pa.us>
2004-06-29 16:27 ` Re: FW: "=" operator vs. "IS" Peter Eisentraut <peter_e@gmx.net>
2004-06-29 17:37 ` Re: FW: "=" operator vs. "IS" Greg Stark <gsstark@mit.edu>
2 siblings, 4 replies; 15+ messages in thread
From: Rich Hall @ 2004-06-29 14:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-29 14:28 ` Re: FW: "=" operator vs. "IS" Rich Hall <rhall@micropat.com>
@ 2004-06-29 14:47 ` Michael Kleiser <mkl@webde-ag.de>
3 siblings, 0 replies; 15+ messages in thread
From: Michael Kleiser @ 2004-06-29 14:47 UTC (permalink / raw)
To: Rich Hall <rhall@micropat.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-29 14:28 ` Re: FW: "=" operator vs. "IS" Rich Hall <rhall@micropat.com>
@ 2004-06-29 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2004-06-29 14:57 UTC (permalink / raw)
To: Rich Hall <rhall@micropat.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-29 14:28 ` Re: FW: "=" operator vs. "IS" Rich Hall <rhall@micropat.com>
@ 2004-06-29 16:27 ` Peter Eisentraut <peter_e@gmx.net>
3 siblings, 0 replies; 15+ messages in thread
From: Peter Eisentraut @ 2004-06-29 16:27 UTC (permalink / raw)
To: Rich Hall <rhall@micropat.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-29 14:28 ` Re: FW: "=" operator vs. "IS" Rich Hall <rhall@micropat.com>
@ 2004-06-29 17:37 ` Greg Stark <gsstark@mit.edu>
3 siblings, 0 replies; 15+ messages in thread
From: Greg Stark @ 2004-06-29 17:37 UTC (permalink / raw)
To: Rich Hall <rhall@micropat.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: "=" operator vs. "IS"
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
@ 2004-07-01 15:02 ` Jeff Boes <jboes@qtm.net>
2 siblings, 0 replies; 15+ messages in thread
From: Jeff Boes @ 2004-07-01 15:02 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2004-07-01 15:02 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-28 22:02 "=" operator vs. "IS" Stefan Weiss <spaceman@foo.at>
2004-06-28 22:27 ` Steve Crawford <scrawford@pinpointresearch.com>
2004-06-28 22:28 ` Michael A Nachbaur <mike@nachbaur.com>
2004-06-28 23:00 ` terry@ashtonwoodshomes.com
2004-06-28 22:17 FW: "=" operator vs. "IS" Dmitri Bichko <dbichko@genpathpharma.com>
2004-06-28 23:02 ` Stefan Weiss <spaceman@foo.at>
2004-06-28 23:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-29 17:33 ` Greg Stark <gsstark@mit.edu>
2004-06-29 17:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-29 14:28 ` Rich Hall <rhall@micropat.com>
2004-06-29 14:47 ` Michael Kleiser <mkl@webde-ag.de>
2004-06-29 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-29 16:27 ` Peter Eisentraut <peter_e@gmx.net>
2004-06-29 17:37 ` Greg Stark <gsstark@mit.edu>
2004-07-01 15:02 ` Jeff Boes <jboes@qtm.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox