X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BAD5552AC0 for ; Fri, 8 Jul 2005 07:08:26 -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 80313-05 for ; Fri, 8 Jul 2005 10:08:24 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.192]) by svr1.postgresql.org (Postfix) with ESMTP id 3EC8C529B6 for ; Fri, 8 Jul 2005 07:08:23 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id n29so180969nzf for ; Fri, 08 Jul 2005 03:08:25 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=D4vlXqKUy5fC7mnzk/E23/7y7tSVCk66iSAD9+kTtTR05ILYypSFiWfipzUF9HAqHoEcEozB/IV73RaKSNWi5IWQ3UY6ucdN36PsJNt7JqBdS8ohC2yKaIwzPflI5Mhuwg9YOibIwMd6ji92nqxiC5wAFXhtlngbEqdcGWQvj1U= Received: by 10.36.141.10 with SMTP id o10mr669188nzd; Fri, 08 Jul 2005 03:08:25 -0700 (PDT) Received: by 10.36.22.20 with HTTP; Fri, 8 Jul 2005 03:08:25 -0700 (PDT) Message-ID: <758d5e7f0507080308232fde7f@mail.gmail.com> Date: Fri, 8 Jul 2005 12:08:25 +0200 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: =?ISO-8859-1?Q?D=FCster_Horst?= Subject: Re: where with NULL values are not selected Cc: "pgsql-admin@postgresql.org" In-Reply-To: <5B025B1F39D6D4119F5700508BEEEC6603DE3D8C@srsofaioi4546.ktso.ch> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <5B025B1F39D6D4119F5700508BEEEC6603DE3D8C@srsofaioi4546.ktso.ch> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.611 tagged_above=0 required=5 tests=AWL, FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/61 X-Sequence-Number: 18117 On 7/8/05, D=FCster Horst wrote: > I try to join two tables whereas some column values do have NULL values w= ith > the following query: >=20 > select table1.column from table1, table2 where table1.column=3Dtable2.col= umn >=20 > table1.column and table2.column may have NULL values. The problem is that > these columns where not selected. Does there exists any solution to > select/join the NULL value colums also. A hint: qnex=3D# SELECT 'ok' WHERE NULL=3DNULL; ?column? (0 rows) In other words -- NULL is not equal to NULL. NULL is not a value, NULL is a state. If you want to join those columns, you cannot use NULL as a joining key. It's the way SQL works. Anyway, a quick-and-dirty solution might be: select table1.column from table1, table2 where coalesce(table1.column,-1)=3Dcoalesce(table2.column, -1); ...assuming column doesn't have '-1' value. A better solution would be to rethink your design (don't use NULLs). Incientally, instead of using this syntax: select table1.column from table1, table2 where table1.column=3Dtable2.colum= n; ...try using explicit inner joins: select table1.column from table1 JOIN table2 USING(column); Regards, Dawid