O'Reilly logo

SQL in a Nutshell, 3rd Edition 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

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

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