Thread: Antw: LEFT JOIN

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

> 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

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.idfrom firstexceptselect second.id_firstfrom second;

erol

> Antti

Antti Linno <alligator@all.ee> 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