FROM Clause

Syntax

table-reference join-type table-reference

Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.

Syntax

column-reference = column-reference

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Semantics

column-reference

refers to a column of one the tables specified in the FROM clause.

Notes

Self-joins are not allowed.

Semantics

table-reference

is a table-primary or another joined-table.

join-type

is one of the following:

INNER JOIN

LEFT [ OUTER ] JOIN

RIGHT [ OUTER ] JOIN

join-predicate

for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.

for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.

Notes

A query that uses INNER JOIN syntax in the FROM clause produces the same result set as a query that uses the WHERE clause to state the join-predicate.
The outer table in an OUTER JOIN is the anchor table.
Note to reviewers: "anchor table" may not be the right term. See note in Outer Joins.
Self outer joins are not allowed.

Syntax

{ table-name [ AS ] alias

[ ( column-alias [ , ...] ) ] [ , ...] ]

| ( joined-table ) }

Syntax

table-reference join-type table-reference

ON join-predicate

Syntax

column-reference = column-reference

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Semantics

column-reference

refers to a column of one the tables specified in the FROM clause.

Notes

Self-joins are not allowed.

Semantics

table-reference

is a table-primary or another joined-table.

join-type

is one of the following:

INNER JOIN

LEFT [ OUTER ] JOIN

RIGHT [ OUTER ] JOIN

join-predicate

for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.

for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.

Notes

A query that uses INNER JOIN syntax in the FROM clause produces the same result set as a query that uses the WHERE clause to state the join-predicate.
The outer table in an OUTER JOIN is the anchor table.
Note to reviewers: "anchor table" may not be the right term. See note in Outer Joins.
Self outer joins are not allowed.

Semantics

table-name	specifies a table in the logical schema. Vertica selects a suitable projection to use.
alias	specifies a temporary name to be used for references to the table.
column-alias	specifies a temporary name to be used for references to the column.
joined-table	specifies an outer join.

Semantics

table-primary	specifies an optionally qualified table name with optional table aliases, column aliases, and outer joins.
joined-table	specifies an outer join.

Syntax

table-primary | joined-table

Syntax

table-reference join-type table-reference

ON join-predicate

Syntax

column-reference = column-reference

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Semantics

column-reference

refers to a column of one the tables specified in the FROM clause.

Notes

Self-joins are not allowed.

Semantics

table-reference

is a table-primary or another joined-table.

join-type

is one of the following:

INNER JOIN

LEFT [ OUTER ] JOIN

RIGHT [ OUTER ] JOIN

join-predicate

for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.

for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.

Notes

A query that uses INNER JOIN syntax in the FROM clause produces the same result set as a query that uses the WHERE clause to state the join-predicate.
The outer table in an OUTER JOIN is the anchor table.
Note to reviewers: "anchor table" may not be the right term. See note in Outer Joins.
Self outer joins are not allowed.

Syntax

{ table-name [ AS ] alias

[ ( column-alias [ , ...] ) ] [ , ...] ]

| ( joined-table ) }

Syntax

table-reference join-type table-reference

ON join-predicate

Syntax

column-reference = column-reference

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Semantics

column-reference

refers to a column of one the tables specified in the FROM clause.

Notes

Self-joins are not allowed.

Semantics

table-reference

is a table-primary or another joined-table.

join-type

is one of the following:

INNER JOIN

LEFT [ OUTER ] JOIN

RIGHT [ OUTER ] JOIN

join-predicate

for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.

for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.

Notes

A query that uses INNER JOIN syntax in the FROM clause produces the same result set as a query that uses the WHERE clause to state the join-predicate.
The outer table in an OUTER JOIN is the anchor table.
Note to reviewers: "anchor table" may not be the right term. See note in Outer Joins.
Self outer joins are not allowed.

Semantics

table-name	specifies a table in the logical schema. Vertica selects a suitable projection to use.
alias	specifies a temporary name to be used for references to the table.
column-alias	specifies a temporary name to be used for references to the column.
joined-table	specifies an outer join.

Semantics

table-primary	specifies an optionally qualified table name with optional table aliases, column aliases, and outer joins.
joined-table	specifies an outer join.