Thread: Oracle to PostgreSQL help: What is (+) in Oracle select?

I'm trying to modify an application which runs on Oracle to run against 
PostgreSQL.  I'm currently stuck on a query that I can't recognize, it 
doesn't look like standard SQL.

A select is done across two tables, however when joining the foreign 
key, the right hand side of the equallity has (+) appended

SELECT o.* from one o, two t where o.key = t.key(+)

Does anyone know what this does and how I can reproduce the select in 
PostgreSQL?

Thanks
Chris

This is Oracle's syntax for an outer join.  Try this in PostgreSQL

SELECT o.* from one o LEFT JOIN two t ON o.key = t.key;

-----Original Message-----
From:    Christopher Audley [SMTP:Christopher.D.Audley@jhu.edu]
Sent:    Friday, March 16, 2001 3:57 PM
To:    pgsql-sql@postgresql.org
Subject:    Oracle to PostgreSQL help:  What is (+) in Oracle select?

I'm trying to modify an application which runs on Oracle to run against 
PostgreSQL.  I'm currently stuck on a query that I can't recognize, it 
doesn't look like standard SQL.

A select is done across two tables, however when joining the foreign 
key, the right hand side of the equallity has (+) appended

SELECT o.* from one o, two t where o.key = t.key(+)

Does anyone know what this does and how I can reproduce the select in 
PostgreSQL?

Thanks
Chris


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> A select is done across two tables, however when joining the foreign
> key, the right hand side of the equallity has (+) appended
>
> SELECT o.* from one o, two t where o.key = t.key(+)
>
> Does anyone know what this does and how I can reproduce the select in
> PostgreSQL?

Hi Chris,

The (+) in Oracle is for an outer join. See
http://www.postgresql.org/devel-corner/docs/postgres/sql-select.html , in
the join-type description, left outer join. Outer joins are only available
in PostgreSQL 7.1, which is currently in the late stages of beta testing.

Hope this helps,

Joe

On Fri, Mar 16, 2001 at 05:57:14PM -0500, Christopher Audley wrote:
> I'm trying to modify an application which runs on Oracle to run against 
> PostgreSQL.  I'm currently stuck on a query that I can't recognize, it 
> doesn't look like standard SQL.
> 
> A select is done across two tables, however when joining the foreign 
> key, the right hand side of the equallity has (+) appended
> 
> SELECT o.* from one o, two t where o.key = t.key(+)
> 
> Does anyone know what this does and how I can reproduce the select in 
> PostgreSQL?

It's an outer join. In Postgres it'd be

SELECT o.* from one left outer join two using ( key )

but it's new in 7.1 .

Richard