SQL in a Nutshell, 2nd Edition

Name

JOIN Subclause

Synopsis

The JOIN subclause enables you to retrieve rows from two or more logically related tables. You are able to define many different join conditions and types of joins, though the types of joins supported by the different platforms vary greatly.

Platform	Command
DB2	Supported, with limitations
MySQL	Supported, with variations
Oracle	Supported
PostgreSQL	Supported, with variations
SQL Server	Supported, with limitations

SQL2003 Syntax

FROM table [AS alias] {CROSS JOIN |
{ [NATURAL] [join_type] JOIN joined_table [AS alias]
   { ON join_condition1 [{AND|OR} join_condition2] [...] ] |
     USING (column1 [,...]) } }
[...]

Keywords

FROM table

Defines the first table or view in the join.

NATURAL

Specifies that the join (either inner or outer) should be assumed on the tables using all columns of identical name shared between the two tables. Consequently, you should not specify join conditions using the ON or USING clauses. The query will fail if you issue a natural join on two tables that do not contain any columns with the same name(s).

join_type JOIN joined_table

Specifies the type of JOIN and the second (and any subsequent) table in the join. You may also define an alias on any of the joined_tables. The join types are:

CROSS JOIN

Specifies the complete cross product of two tables. For each record in the first table, all the records in the second table are joined, creating a huge result set. This command has the same effect as leaving off the join condition, and ...