ASA Getting Started
  Selecting Data from Multiple Tables

Joining tables using natural joins

The NATURAL JOIN operator joins two tables based on common column names. In other words, Adaptive Server Anywhere generates an ON clause that equates the common columns from each table.

List all employees and their departments

• In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

  SELECT emp_lname, dept_name
  FROM employee NATURAL JOIN department

  emp_lname	dept_name
  Whitney	R & D
  Cobb	R & D
  Breault	R & D
  Shishov	R & D
  Driscoll	R & D
  ...	...

Adaptive Server Anywhere looks at the two tables and determines that the only column name they have in common is dept_id. The following ON CLAUSE is internally generated and used to perform the join:

FROM employee JOIN department
   ON employee.dept_id = department.dept_id

NATURAL JOIN is just a shortcut for typing the ON clause; the two queries are identical.

Errors using NATURAL JOIN 

This join operator can cause problems by equating columns you may not intend to be equated. For example, the following query generates unwanted results:

SELECT *
FROM sales_order NATURAL JOIN customer

The result of this query has no rows. Adaptive Server Anywhere internally generates the following ON clause:

FROM sales_order JOIN customer
   ON sales_order.id = customer.id

The id column in the sales_order table is an ID number for the order. The id column in the customer table is an ID number for the customer. None of them matched. Of course, even if a match were found, it would be a meaningless one.

For more information, see Natural joins.