Natural join

A natural join is a join statement that compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.

Note: Natural joins may cause problems if columns are added or renamed. It is highly recommended to not use them.

Examples

This is the same as an equi join on (emp.deptno = dept.deptno) (using SCOTT's table):

SCOTT>  SELECT dname, ename FROM dept NATURAL JOIN emp;

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

Same query on the HR sample schema:

SELECT department_name, first_name||' '||last_name
  FROM departments NATURAL JOIN employees;

Also see

• Cartesian join
• Equi join
• Inner join
• Nonequi join
• Outer 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	#