problems with left outer join

From: "Andreas Andreakis" <andreas(dot)andreakis(at)googlemail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: problems with left outer join
Date: 2006-10-05 08:13:34
Message-ID: 85e6ec170610050113p7ebe436ej618481100b848a5e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I would like to make a left outer join between two tables (A and B) and then
only select rows of table A which do not apply on the join condition (where
B is null)

simple example:

create table A(
id int primary key
);

create table B(
id int primary key
);

insert into A values(1);
insert into A values(2);
insert into B values(1);

The goal is now to make a join between A and B and to select only the row
from A with the id = 2 (because there is no corresponding id in Table B)

So I tried:

select *
from A t1 left outer join B t2 on t1.id = t2.id
where t2.id = null

but I dont get any row returned. I expect the result: 2 | NULL

if I execute

select *
from A t1 left outer join B t2 on t1.id = t2.id

I get:
1) 2 | NULL
2) 1 | 1

what am I doing wrong ?
thanx in advance,
Andreas

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2006-10-05 11:14:34 Re: postgresql db account
Previous Message Stuart Bishop 2006-10-05 07:07:05 Re: Conditional INSERT: if not exists