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.
For example, for the following query:
SELECT emp_lname, dept_name FROM employee NATURAL JOIN department
the database server looks at the two tables and determines that the only column name they have in common is dept_id. The following ON phrase is internally generated and used to perform the join:
FROM employee JOIN department ... ON employee.dept_id = department.dept_id
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.
The database server internally generates the following ON phrase:
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.
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.
