O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required