From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | Ant9000 <ant9000(at)seldati(dot)it>, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] OUTER JOINs in PostgreSQL |
Date: | 1999-06-06 12:06:14 |
Message-ID: | l03130303b380144a99e0@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
At 23:47 +0300 on 02/06/1999, Ant9000 wrote:
> The best I was able to obtain is this:
>
> SELECT master.*,detail.lastvisit,detail.info FROM master,detail
> WHERE master.id=detail.id
> UNION
> SELECT *,NULL AS lastvisit,NULL AS info FROM master
> WHERE id NOT IN (SELECT id FROM detail);
>
> which is (at best) unelegant; in MS Access you could do something like
>
> SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
> ON master.id=detail.id;
>
> With Oracle, there's an even shorter solution:
>
> SELECT master.*,detail.lastvisit,detail.info FROM master,detail
> WHERE master.id=detail.id(+);
>
>
> Is anything like that available with PostgreSQL?
No, outer joins are not supported in PostgreSQL. Your solution is the
accepted workaround, although I tend to recommend WHERE NOT EXISTS ( select
* from detail WHERE detail.id = master.id )in the second clause - it is
more efficient.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
From | Date | Subject | |
---|---|---|---|
Next Message | Herouth Maoz | 1999-06-06 12:26:21 | Re: [SQL] Howto convert floats to text? |
Previous Message | Chris Bitmead | 1999-06-06 10:59:12 | Re: [SQL] Slashdot Query |