User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Joins using comparisons

You can specify a join condition for any join type except CROSS JOIN. Alternatively you can create a join using a condition instead of a keyword, instead of using a KEY or NATURAL JOIN. You specify a join condition by inserting an ON phrase immediately adjacent to the join to which it applies.

Natural joins and key joins use generated join conditions; that is, the keyword KEY or NATURAL indicates a restriction on the join results.

For a natural join, the generated join condition is based on the names of columns in the tables being joined; for a key join, the condition is based on a foreign key relationship between the two tables.

In the sample database, the following are logically equivalent:

SELECT *
FROM sales_order JOIN customer   
   ON sales_order.cust_id = customer.id
SELECT *
FROM sales_order KEY JOIN customer

The following two are also equivalent:

SELECT *
FROM department JOIN employee
   ON department.dept_id = employee.dept_id
SELECT *
FROM department NATURAL JOIN employee

When you join two tables, the columns you compare must have the same or similar data types.

Join types 

There are several types of joins, such as equijoins, natural joins, and outer joins. The most common join, the equijoin, is based on equality. The following command lists each order number and the name of the customer who placed them.

SELECT sales_order.id, customer.fname, customer.lname
FROM sales_order JOIN customer
   ON sales_order.cust_id = customer.id

The condition for joining the values in two columns does not need to be equality (=). You can use any of the other comparison operators: not equal (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).

  Using the WHERE clause in join statements

You can use the WHERE clause to determine which rows are included in the results. In this role, it acts exactly like it does when using a single table, selecting only the rows that interest you.

The WHERE clause can also specify the connection between the tables and views named in the FROM clause. In this role, it acts somewhat like the ON phrase. In fact in the case of inner joins, the behavior is identical. However, in outer joins, for example, the same condition can produce different results if moved from an ON phrase to the WHERE clause because null values are treated differently in these two contexts. The ON phrase allows you to isolate the join constraints and can make your join statement easier to read.