Beginner help - struggling with natural join example
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