OUTER JOIN

From: "Marco Roda" <MarcoRoda(at)amdosoft(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: OUTER JOIN
Date: 2003-04-17 14:21:36
Message-ID: 000401c304ec$a77547e0$0301a8c0@amdosoft.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hallo,

I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
problems with outer joins.

Here are the tables:
select * from shift_type
id | order_num | from_time | to_time | disabled
----+-----------+-----------+----------+----------
1 | 1 | 06:00:00 | 14:00:00 | f
2 | 2 | 14:00:00 | 22:00:00 | f
3 | 3 | 22:00:00 | 06:00:00 | t
6 | 6 | 00:00:00 | 23:00:00 | f

select * from shift_desc
app_language_id | shift_type_id | description
-----------------+---------------+-------------
1 | 1 | Morning
1 | 2 | Afternoon
1 | 3 | Night
2 | 6 | SHIFT_DE
1 | 6 | SHIFT_EN

and here is the SQL for Oracle:
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST, shift_desc SD
WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
ORDER BY ST.order_num

The expected result should be:
id | order_num | from_time | to_time | disabled | description
----+-----------+-----------+----------+----------+-------------
1 | 1 | 06:00:00 | 14:00:00 | f |
2 | 2 | 14:00:00 | 22:00:00 | f |
3 | 3 | 22:00:00 | 06:00:00 | t |
6 | 6 | 00:00:00 | 23:00:00 | f | SHIFT_DE
with description populated with NULL, as app_language_id not found.

It seems that Oracle's outer joins work on the basis the single record,
while PostgreSQL don't.
How to do it?

Thank you for your attention,
Marco Roda

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-04-17 14:26:43 Re: planner question..
Previous Message Tom Lane 2003-04-17 14:17:31 Re: analyse question..