Braindump
Oracle Join Syntax
Braindump.OracleJoinSyntax History
Show minor edits - Show changes to output
January 17, 2010, at 01:53 PM
by -
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
@]
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
@]