Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Beginner help - struggling with natural join example

nick woodwardMar 18 2013 — edited Mar 19 2013
Hi,

Apologies for how basic this query will likely be, but I'm teaching myself from scratch and don't like to move from topic to topic without a good understanding of why I'm getting the results I'm getting, rather than just knowing 'that's the way it's done'.

I'm using the official exam guide for 1z0-051, and on page 320 there is the following exercise:

+"The JOB_HISTORY table shares three identically named columns with the EMPLOYEES table: EMPLOYEE_ID, JOB_ID, and DEPARTMENT_ID. You are required to describe the tables and fetch the EMPLOYEE_ID, JOB_ID,+
+DEPARTMENT_ID, LAST_NAME, HIRE_DATE, and END_DATE values for all rows retrieved using a pure natural join. Alias the EMPLOYEES table as EMP and the JOB_HISTORY table as JH and use dot notation where possible."+


Their solution to this (which is pretty much what I initially came up with) is:

SELECT employee_id, job_id, department_id, emp.last_name, emp.hire_date, JH.end_date
FROM job_history JH
NATURAL JOIN employees emp;

This results in the single employee 'Taylor' being returned.

------------------------------

Is there any chance someone could 'hold my hand' through this example please? I have no idea why only one result is returned, but additionally I don't know what else I was expecting - clearly my understanding of the natural join clause is severely lacking. The book states that:

+"Executing this statement returns a single row with the same EMPLOYEE_ID, JOB_ID, and DEPARTMENT_ID values in both tables"+

I guess I'm confused because I thought that the join clauses were to enable additional content from other tables to be displayed, not to limit the rows - which is what it sounds like they mean by "returns a single row with the same employee_id, job_id, and department_id values in both tables".



I'm very confused!!!

Thanks in advance,

Nick
This post has been answered by Frank Kulash on Mar 18 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2013
Added on Mar 18 2013
11 comments
1,007 views