I'm a newbie to SQL and I have been studying the OCA Oracle Database 12c SQL Fundamentals book, but a section on Natural Joins in confusing me.
Basically the author says:
SELECT r.region_name, c.country_name, l.city, d.department_name
FROM departments d
NATURAL JOIN locations l, countries c, regions r;
The natural join between DEPARTMENTS and LOCATIONS creates an interim result set consisting of 27 rows since they are implicitly joined on the LOCATION_ID column. This set is then Cartesian-joined to the COUNTRIES table since a join condition is not implicitly or explicitly specified. The 27 interim rows are joined to the 25 rows in the COUNTRIES table, yielding a new interim results set with 675 (27 × 25) rows and three columns: DEPARTMENT_NAME, CITY, and COUNTRY_NAME. This set is then joined to the REGIONS table. Once again, a Cartesian join occurs because the REGION_ID column is absent from any join condition. The final result set contains 2700 (675 × 4) rows and four columns.
This I can understand because you are evaluating the joins from left to right. But then he writes:
The JOIN…USING and JOIN…ON syntaxes are better suited for joining multiple tables. The following query joins four tables using the natural join syntax:
SELECT region_id, country_id, c.country_name, l.city, d.department_name
FROM departments d
NATURAL JOIN locations l
NATURAL JOIN countries c
NATURAL JOIN regions r;
This query correctly yields 27 rows in the final results set since the required join columns are listed in the SELECT clause. The following query demonstrates how the JOIN…ON clause is used to fetch the same 27 rows. A join condition can reference only columns in its scope. In the following example, the join from DEPARTMENTS to LOCATIONS may not reference columns in the COUNTRIES or REGIONS tables, but the join between COUNTRIES and REGIONS may reference any column from the four tables involved in the query.
This second part method of writing Natural Joins confuses me. I do not understand the underlying logic behind the 2nd set of Natural Join statements. To me it seems like the first set and the 2nd set look the same, but apparently they are not.
Can anyone tell the me differences?
Thanks!!