ASA SQL User's Guide
Joins: Retrieving Data from Several Tables
When you specify a natural join, Adaptive Server Anywhere generates a join condition based on columns with the same name. For this to work in a natural join of base tables, there must be at least one pair of columns with the same name, with one column from each table. If there is no common column name, an error is issued.
If table A and table B have one column name in common, and that column is called x, then
SELECT * FROM A NATURAL JOIN B
is equivalent to the following:
SELECT * FROM A JOIN B ON A.x = B.x
If table A and table B have two column names in common, and they are called a and b, then A NATURAL JOIN B
is equivalent to the following:
A JOIN B ON A.a = B.a AND A.b = B.b
For example, you can join the employee and department tables using a natural join because they have a column name in common, the dept_id column.
SELECT emp_fname, emp_lname, dept_name FROM employee NATURAL JOIN department ORDER BY dept_name, emp_lname, emp_fname
emp_fname | emp_lname | dept_name |
---|---|---|
Janet | Bigelow | Finance |
Kristen | Coe | Finance |
James | Coleman | Finance |
Jo Ann | Davidson | Finance |
... | ... | ... |
The following statement is equivalent. It explicitly specifies the join condition that was generated in the previous example.
SELECT emp_fname, emp_lname, dept_name FROM employee JOIN department ON (employee.dept_id = department.dept_id) ORDER BY dept_name, emp_lname, emp_fname
Natural joins with an ON phrase
Natural joins of table expressions
Natural joins of views and derived tables