SQL in a Nutshell, 3rd Edition

Name

JOIN Subclause

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

Platform	Command
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(s) 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 its result set is also known as a “Cartesian product.” Cross joins are not ...