Joining tables using natural joins

The NATURAL JOIN operator joins two tables based on common column names. In other words, Sybase IQ generates a WHERE clause that equates the common columns from each table.

Example

For example, for the following query:

SELECT Surname, 
  DepartmentName
FROM Employees
NATURAL JOIN Departments

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

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

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 SalesOrders
NATURAL JOIN Customers

The result of this query has no rows.

The database server internally generates the following ON phrase:

FROM SalesOrders JOIN Customers
	ON SalesOrders.ID = Customers.ID

The id column in the SalesOrders 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.

You should be careful using join operators. Always remember that the join operator just saves you from typing the WHERE clause for an unenforced foreign key or common column names. Be mindful of the WHERE clause, or you may create queries that give results other than what you intend.