'WHERE' syntax equivalent of LEFT OUTER JOIN in PostgreSQL

Question

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.

postgresql.org/message-id/003e01c1d65f$b9a01d80$01011eac@irina — Hisham
– Hisham, Commented Oct 22, 2013 at 9:22
Thanks, pretty useful, but in the following link there is an example of "left join + where clause", not an example of replacing "join" with "where" — DraggonZ
– DraggonZ, Commented Oct 22, 2013 at 9:28

score 11 · Accepted Answer · 2013-10-22 11:30:03Z

11

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)

edited Oct 22, 2013 at 11:30

answered Oct 22, 2013 at 9:49

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

DraggonZ Over a year ago

As i understand, there is no "Oracle (+)" like equivalent in PostgreSQL?

Clodoaldo Neto Over a year ago

@DraggonZ No, there isn't. Why do you want that? To not have to rewrite the Oracle query?

DraggonZ Over a year ago

@Clodoaldo Neto To not to rewrite much existing PostgreSQL query. That query dynamically builds from C# code (something like whereString += tableName + ".id = " + table.Table + "." + tableName + "_id AND ";) It's a legacy code without tests, and I need to replace some wheres with left joins without breaking any other logic.

user330315 Over a year ago

@DraggonZ: no there is no such operator in Postgres - and you shouldn't be using it in Oracle either.

DraggonZ Over a year ago

@a_horse_with_no_name Could you move this to your answer? I'll accept it.

Collectives™ on Stack Overflow

'WHERE' syntax equivalent of LEFT OUTER JOIN in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related