X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 85C1FD1B187
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jun 2004 19:59:47 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78839-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jun 2004 22:59:41 +0000 (GMT)
Received: from mail.greatgulfhomes.com (mail.greatgulfhomes.com
	[216.191.52.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 2250AD1B19C
	for <pgsql-sql@postgresql.org>; Mon, 28 Jun 2004 19:59:36 -0300 (ADT)
Received: from terrynew2 (unknown [216.191.52.74])
	by mail.greatgulfhomes.com (Postfix) with SMTP
	id 6B8853664CF; Mon, 28 Jun 2004 19:06:40 -0400 (EDT)
From: <terry@ashtonwoodshomes.com>
To: "'Michael A Nachbaur'" <mike@nachbaur.com>,
	"'Stefan Weiss'" <spaceman@foo.at>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: "=" operator vs. "IS"
Date: Mon, 28 Jun 2004 19:00:13 -0400
Message-ID: <016701c45d63$ac859e80$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-reply-to: <200406281528.08526.mike@nachbaur.com>
X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME
X-Spam-Level: 
X-Archive-Number: 200406/282
X-Sequence-Number: 18227

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
>