> On Sat, Nov 04, 2000 at 03:13:26PM -0500, Bruce Momjian wrote:
> > Thanks. I will update it as soon as 7.1 goes beta.
> > >
> > > 4.24) How do I do an outer join?
> > >
> > > Outer joins are supported in 7.1.
> > >
>
> Perhaps throw in an example of the supported syntax? From what I've seen
> discussed here, people coming from other systems will find their outer
> join queries failing, and may end up at this question.
Good idea. Here is an updated FAQ item on outer joins. It will appear
on the web site as soon as I can connect.
---------------------------------------------------------------------------
4.24) How do I do an outer join?
PostgreSQL 7.1 and later supports outer joins. Here are two examples:
SELECT *
FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
or
SELECT *
FROM t1 LEFT OUTER JOIN t2 USING (col);
These identical queries join t1.col to t2.col, and return any unjoined
rows in t1. A RIGHT join would return unjoined
rows of table t2. A FULL join would return unjoined rows from t1 and t2.
The word OUTER is optional and is
assumed in LEFT, RIGHT, and FULL joins. Ordinary joins are called INNER
joins.
In previous releases, outer joins can be simulated using UNION and NOT
IN. For example, when joining tab1 and
tab2, the following query does an outer join of the two tables:
SELECT tab1.col1, tab2.col2
FROM tab1, tab2
WHERE tab1.col1 = tab2.col1
UNION ALL
SELECT tab1.col1, NULL
FROM tab1
WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
ORDER BY tab1.col1
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
