Outer join

An outer join is similar to the equi join, but Oracle will also return non matched rows from the table.

Oracle 9i introduced the ANSI/ISO 1999 standard syntax for specifying outer joins with the LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN clauses. The legacy syntax is to use a (+) in the query's WHERE clause, similar to SQL Server's (*) syntax.

Examples

Using Oracle's legacy join syntax:

SELECT e.ename, d.dname
  FROM emp e, dept d
 WHERE e.deptno = d.deptno(+);

SELECT e.ename, d.dname
  FROM emp e, dept d
 WHERE e.deptno(+) = d.deptno; 

Note: The (+) sign indicates that in case the column contains a null it should also be included.

Using ANSI join syntax:

SELECT e.ename, d.dname
  FROM emp e LEFT OUTER JOIN dept d
    ON (e.deptno = d.deptno);

SELECT e.ename, d.dname
  FROM emp e RIGHT OUTER JOIN dept d
    ON (e.deptno = d.deptno);

Full outer joins can only be performed using the ANSI syntax:

SELECT e.ename, d.deptno
  FROM emp e FULL OUTER JOIN dept d
    ON (e.deptno = d.deptno);

Also see

Articles:

• Common errors seen when using OUTER-JOIN

Other join methods:

• Cartesian join
• Equi join
• Inner join
• Natural join
• Nonequi join
• Self join

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#