CODEWITHSUNDEEP
sqloracle-databasejoinnatural-join
user6611026
user6611026

Reputation:

What is the difference between Join and Natural Join?

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.

Upvotes: 3

Views: 7411

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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.

Upvotes: 11

Related Questions