Outer join

An outer join is a join 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

With standard SCOTT's table.

Using Oracle's legacy join syntax:

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

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
SMITH      RESEARCH
JAMES      SALES
TURNER     SALES
BLAKE      SALES
MARTIN     SALES
WARD       SALES
ALLEN      SALES

14 rows selected.

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

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
JONES      RESEARCH
FORD       RESEARCH
ADAMS      RESEARCH
SMITH      RESEARCH
SCOTT      RESEARCH
WARD       SALES
TURNER     SALES
ALLEN      SALES
JAMES      SALES
BLAKE      SALES
MARTIN     SALES
           OPERATIONS

15 rows selected.

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	#