Braindump

Oracle Join Syntax

Braindump.OracleJoinSyntax History

Show minor edits - Show changes to markup

January 17, 2010, at 01:53 PM by 83.77.90.209 -
Added lines 1-31:

Oracle offers an IMHO strange (to me) syntax to support joins right in WHERE clauses.

You can interpret the (+) operator like this: "Extend the original result set with NULL rows, if the join condition is not met."

See also:

  • http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm
  • http://www.adp-gmbh.ch/ora/sql/outer_join.html

Inner Join

SELECT *
  FROM A, B
 WHERE A.SOME_FIELD = B.ANOTHER_FIELD -- simple join / equi join: only matching rows

Left outer Join

SELECT *
  FROM A, B
 WHERE A.SOME_FIELD = B.ANOTHER_FIELD(+) -- all rows from A, rows from B on match or NULL otherwise

Right outer Join

SELECT *
  FROM A, B
 WHERE A.SOME_FIELD(+) = B.ANOTHER_FIELD -- all rows from B, rows from A on match or NULL otherwise
Restore