Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Joins: Retrieving Data from Several Tables

Joins involving derived tables Next Page

Natural joins


When you specify a natural join, SQL 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;
Example 1

For example, you can join the Employees and Departments tables using a natural join because they have a column name in common, the DepartmentID column.

SELECT GivenName, Surname, DepartmentName
FROM Employees NATURAL JOIN Departments
ORDER BY DepartmentName, Surname, GivenName;
GivenName Surname DepartmentName
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 GivenName, Surname, DepartmentName
FROM Employees JOIN Departments
  ON (Employees.DepartmentID = Departments.DepartmentID)
ORDER BY DepartmentName, Surname, GivenName;
Example 2

In Interactive SQL, execute the following query:

SELECT Surname, DepartmentName
FROM Employees NATURAL JOIN Departments;
Surname DepartmentName
Whitney R & D
Cobb R & D
Breault R & D
Shishov R & D
Driscoll R & D
... ...

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

FROM Employees JOIN Departments
   ON Employees.DepartmentID = Departments.DepartmentID

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


Errors using NATURAL JOIN
Natural joins with an ON clause
Natural joins of table expressions
Natural joins of views and derived tables