Contents Index Natural joins Natural joins of table expressions

ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Natural joins

Natural joins with an ON phrase


When you specify a NATURAL JOIN and put a join condition in an ON phrase, the result is the conjunction of the two join conditions.

For example, the following two queries are equivalent. In the first query, Adaptive Server Anywhere generates the join condition employee.dept_id    =    department.dept_id. The query also contains an explicit join condition.

SELECT emp_fname, emp_lname, dept_name
FROM employee NATURAL JOIN department
  ON employee.manager_id = department.dept_head_id

The next query is equivalent. In it, the natural join condition that was generated in the previous query is specified in the ON phrase.

SELECT emp_fname, emp_lname, dept_name
FROM employee JOIN department
  ON employee.manager_id = department.dept_head_id
   AND employee.dept_id = department.dept_id

Contents Index Natural joins Natural joins of table expressions