What is the difference between Join and Natural Join?

Question

I'm learning Oracle SQL and now I'm stuck on Joins chapter. I can't understand the difference between Join and Natural Join

SELECT         employee_id, job_id, department_id,
               e.last_name, e.hire_date, j.end_date
FROM           employees e
NATURAL JOIN   job_history j;

176 SA_REP 80 Taylor 24/03/2006 31/12/2006

SELECT         e.employee_id, e.job_id, e.department_id,
               e.last_name, e.hire_date, j.end_date
FROM           employees e
JOIN           job_history j
ON             (e.department_id = j.department_id)
ORDER BY        employee_id, last_name;

172 SA_REP  80  Bates   24/03/2007  31/12/2006
173 SA_REP  80  Kumar   21/04/2008  31/12/2007
173 SA_REP  80  Kumar   21/04/2008  31/12/2006
174 SA_REP  80  Abel    11/05/2004  31/12/2007
174 SA_REP  80  Abel    11/05/2004  31/12/2006
175 SA_REP  80  Hutton  19/03/2005  31/12/2007
175 SA_REP  80  Hutton  19/03/2005  31/12/2006
176 SA_REP  80  Taylor  24/03/2006  31/12/2007
176 SA_REP  80  Taylor  24/03/2006  31/12/2006
177 SA_REP  80  Livingston  23/04/2006  31/12/2007
177 SA_REP  80  Livingston  23/04/2006  31/12/2006

I don't know why I receive different results if both Join and Natural Join have similar function.

IMO, `Nature Join` use **implicit** join columns that check and join all columns with same name in two tables. With `Join`, you must **explicitly** declare join columns in `ON` statements (maybe same name, maybe not, maybe 2 table has 10 same columns name but just join 2 different columns name, ....). — Pham X. Bach, Jan 11 '17 at 02:52
A good example of why you should never use natural joins. In your case the natural join is using more columns to join on, making it more restrictive. Which columns are those? I don't know as it is not shown! — Nick.Mc, Jan 11 '17 at 04:06
A good example where a short attribute, whose prefix is merely implied by its table name, causes problems when used in relational operators involving other tables e.g. `end_date` in `employees` where the explicit `employment_end_date` would be better. — onedaywhen, Feb 02 '17 at 15:15

score 11 · Accepted Answer · edited Jan 11 '17 at 04:26

Don't use natural join. It is a bug waiting to happen.

An explicit join has an on clause that lists the conditions for matching between the tables. In your example, department_id is for this purpose (although other columns might be available).

The using clause is another very useful alternative. You use it as:

FROM employees e JOIN
     job_history j
     USING (department_id)

It finds department_id in both tables and uses that for the join condition.

NATURAL JOIN adds a JOIN conditions for all columns in the tables that are the same. In your case, this would be department_id plus other columns.

The problem -- as you are experiencing -- is that you don't know what columns are used for the join. Even worse, explicit foreign key references are not used.

Because your query does not specify what is happening, there is lots of scope for mistakes and bugs. There is no actual need for NATURAL JOIN, so you might as well just learn to use ON and USING.

The only situation (and I think there it is useful) when I use `NATURAL JOIN` is in case of Nested Tables, e.g. `SELECT * FROM MY_TABLE NATURAL JOIN TABLE(NESTED_TABLE_COLUMN)` — Wernfried Domscheit, Jan 11 '17 at 06:32

score 2 · Answer 2 · answered Feb 02 '17 at 15:04

Just to make explicit what should be obvious: your first query yields a different result because it is not semantically equivalent to your second query.

Here is a suggested rewite of the NATURAL JOIN version:

WITH e AS 
     ( SELECT employee_id, job_id, department_id,
              last_name, hire_date
         FROM employees ),
     j AS 
     ( SELECT department_id, end_date
         FROM job_history )
SELECT * 
  FROM e NATURAL JOIN j
 ORDER 
    BY employee_id, last_name;

There is no 'bug waiting to happen' in the above query, even though I have -- shock! horror! -- used SELECT * in addition to NATURAL JOIN (both contraindicated by the SQL flock): projecting columns in the CTEs defends your query from the scenario where columns are added to the tables employees and job_history.

Thanks for your help. I've created this post because I'm new in Oracle programming but These days I've reading a little more about SQL joins. Now I'm practicing PLSQL and I'm acquainted more about Oracle SQL. Thanks again!!! — , Feb 02 '17 at 15:36

What is the difference between Join and Natural Join?

2 Answers2