[SQL] LEFT JOIN

[Antti Linno]

Greetings.
 I have a problem. I have 2 tables. E.g. work and workers. I want to
select records, that 1st table has, and the second hasn't(both have id
attribute). I mean I can't do it with is NULL, because those records don't
exist. I was shown, how it is done with mysql

select first.id,second.id_first 
from first left join second on
id=id_first where id_first is NULL;

but when I tried it in psql, it said, not implemented.

Antti

Antw: [SQL] LEFT JOIN

[Gerhard Dieringer]

Antti Linno wrote:
> Greetings.
>  I have a problem. I have 2 tables. E.g. work and workers. I want to
> select records, that 1st table has, and the second hasn't(both have id
> attribute). I mean I can't do it with is NULL, because those records don't
> exist. I was shown, how it is done with mysql

I think, the following select will solve your problem

select  first.id
from first
except
second.id_first 
from second;

>
> select first.id,second.id_first 
> from first left join second on
> id=id_first where id_first is NULL;
> 

This query should never give any result, because you require
id_first = id and id_first is null
but if id_first is null then it's  equal to nothing,
and BTW it's also unequal to nothing.


> but when I tried it in psql, it said, not implemented.

Outer joins (left or right joins) are not implemented in PostgreSQL 7.0, but will
come in 7.1.

> 
> Antti

Gerhard

Re: Antw: [SQL] LEFT JOIN

[Antti Linno]

> I think, the following select will solve your problem
> 
> select  first.id
> from first
> except
> second.id_first 
> from second;
> 
Nay, I got parse error.
Antti

Re: Antw: [SQL] LEFT JOIN

[Erol Oz]

Antti Linno wrote:
> 
> > I think, the following select will solve your problem
> >
> > select  first.id
> > from first
> > except
> > second.id_first
> > from second;
> >
> Nay, I got parse error.

'select' is missing after 'except'.

 select  first.id
 from first
 except
 select second.id_first
 from second;

erol

> Antti

ODP: Antw: [SQL] LEFT JOIN

[Rybarczyk Mikolaj]

Try this:

> Nay, I got parse error.
> Antti
 
 select  first.id
 from first
 except
 select second.id_first 
 from second;
 
Greetings,

Mikolaj. 

Re: Antw: [SQL] LEFT JOIN

[Tom Lane]

Antti Linno <[EMAIL PROTECTED]> writes:
>> I think, the following select will solve your problem
>> 
>> select  first.id
>> from first
>> except
>> second.id_first 
>> from second;
>> 
> Nay, I got parse error.

Should be ... EXCEPT SELECT second.id_first ...

Anyway, we should have full ANSI join support in 7.2, or 7.1 if
we get lucky.

regards, tom lane