CODEWITHSUNDEEP
sqlpostgresqljoinrelational-database
DraggonZ
DraggonZ

Reputation: 1087

'WHERE' syntax equivalent of LEFT OUTER JOIN in PostgreSQL

I know there is a WHERE representation of LEFT JOIN in Oracle that has syntax like:

  FROM t1, t2
 WHERE t1.id = t2.id(+)

instead of:

  FROM t1 LEFT JOIN t2
    ON t1.id = t2.id

Is there anything similar in PostgreSQL? I searched for documentation, but failed to find such feature.

Upvotes: 5

Views: 7480

Answers (1)

user330315
user330315

Reputation:

There is no such operator in Postgres (or standard SQL).

The only way to write an outer join in Postgres is to use an ANSI explicit JOIN syntax:

select *
from table t1
  left join table t2 on t1.id = t2.id;

(or it might be the other way round - it has been ages since I last used the Oracle (+) operator)

More details in the manual: http://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-FROM

You shouldn't be using the (+) operator in Oracle in the first place. Oracle has supported ANSI joins since 9i and Oracle recommends stop using the (+) operator (the above statement will work just fine in Oracle as well)

Upvotes: 11

Related Questions