NATURAL JOIN

NATURAL JOIN CLAUSE

To be able to join two tables using NATURAL JOIN they must have a common column. It compares those common columns with each other and returns the results. It automatically detects the join keys, based on the name of the matching column in both tables. For example in HR schema we can join EMPLOYEES with JOB_HISTORY table using the common columns EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID





The above statement returns only one row as result, because there is just one entry existing in both tables in same time.

Let take now ORDER and CUSTOMERS tables from OE schema. There is only one common column in both tables: customer_id





There is again just one entry presents in both tables in same time.

NATURAL JOIN USING CLAUSE

It allows to join tables on one or more common columns (not on ALL common columns). When we use JOIN USING clause we can specify the join_coulmn(s). In other words we use it when two tables have more than one column with same name but don't to use all of them. It has following syntax:

SELECT table1_column, table2_column…

FROM table1

JOIN table2 USING(join_column1, join_column2…);

Examples:



NATURAL JOIN ON CLAUSE

In some cases we need to join tables without using common column_name. This can be done with JOIN … ON clause.

Example:

It is important to remember that we NEVER can use all three keywords (NATURAL, USING and ON) in one JOIN clause.