Re: [SQL] OUTER JOINs in PostgreSQL

On Sat, 5 Jun 1999, Ant9000 wrote:

> On Wed, 02 Jun 1999, you wrote:
> >I think that you are looking for something like:
> >
> >SELECT m.id, m.name, d.lastvisit, d.info
> > FROM master m, detail d
> > WHERE m.id = d.id;
> >
> >Jason
> >
>
> No, that way I get an inner join: in my example the result would be
>
> id | name | lastvisit | info
> -----------------------------
> 1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
> 1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
>
> and I'm instead looking for this output:
>
> id | name | lastvisit | info
> -----------------------------
> 1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
> 1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
> 2 | Beta | |
> 3 | Gamma | |
>

Try this:

SELECT m.id, m.name, d.lastvisit, d.info
FROM master m, detail d
WHERE m.id = d.id
UNION ALL
SELECT m.id, m.name, 0, 0
FROM master m
WHERE not m.id=ANY(select id from detail);