Join Syntax

Vertica supports the ANSI SQL-92 standard for joining tables, as follows:

table‑reference [join-type] JOIN table-reference [ ON join-predicate ]

where join-type can be one of the following:

For example:

=> SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id;

The ON join-predicate clause is invalid for NATURAL and CROSS joins, required for all other join types.

Alternative Syntax Options

Vertica also supports two older join syntax conventions:

Join specified by WHERE clause join predicate

INNER JOIN is equivalent to a query that specifies its join predicate in a WHERE clause. For example, this example and the previous one return equivalent results. They both specify an inner join between tables T1 and T2 on columns T1.id and T2.id, respectively. 

=> SELECT * FROM T1, T2 WHERE T1.id = T2.id;

Join specified by USING clause

You can join two tables on identically named columns in a USING clause. For example:

=> SELECT * FROM T1 INNER JOIN T2 USING(id);

The INNER keyword is optional; a join that is specified by a USING clause is always an inner join. Thus, this example and the previous ones return equivalent results.

Benefits of SQL-92 Join Syntax

Vertica recommends that you use SQL-92 join syntax for several reasons:

  • SQL-92 outer join syntax is portable across databases; the older syntax was not consistent between databases.
  • SQL-92 syntax provides greater control over whether predicates are evaluated during or after outer joins. This was also not consistent between databases when using the older syntax.
  • SQL-92 syntax eliminates ambiguity in the order of evaluating the joins, in cases where more than two tables are joined with outer joins.