FROM clause

A comma-separated list of data sources to query.

1: Joined-table
2: Table-reference

A comma-separated list of data sources to query.

Syntax

FROM dataset[,...] [ TABLESAMPLE(percent) ]

Parameters

dataset

A set of data to query, one of the following:

Table reference
Joined tables
View
Named subquery: subquery [AS] name

TABLESAMPLE(percent)

Specifies to return a random sampling of records, where percent specifies the approximate sampling size. The percent value must be between 0 and 100, exclusive, and can include decimal values. The number of records returned is not guaranteed to be the exact percentage specified.

All rows of the data have equal opportunities to be selected. Vertica performs sampling before applying other query filters.

Examples

Count all records in customer_dimension table:

=> SELECT COUNT(*) FROM customer_dimension;
 COUNT
-------
 50000
(1 row)

Return a small sampling of rows in table customer_dimension:

=> SELECT customer_name, customer_state FROM customer_dimension TABLESAMPLE(0.5) WHERE customer_state='IL';
    customer_name    | customer_state
---------------------+----------------
 Amy Y. McNulty      | IL
 Daniel C. Nguyen    | IL
 Midori O. Greenwood | IL
 Meghan U. Lampert   | IL
 Tiffany Y. Lang     | IL
 Laura S. King       | IL
 Steve T. Nguyen     | IL
 Craig S. Webber     | IL
 Luigi A. Lewis      | IL
 Mark W. Williams    | IL
(10 rows)

1 - Joined-table

Specifies how to join tables.

Syntax

table-reference [join-type] JOIN table-reference[ TABLESAMPLE(sampling-pct) ] [ ON join-predicate ]

Parameters

table-reference

A table or another joined-table.

join-type

Valid Values:

INNER (default). INNER JOIN is equivalent to a query that specifies its join predicate in a WHERE clause.
LEFT [ OUTER ]
RIGHT [ OUTER ]
FULL [ OUTER ]
NATURAL
CROSS

TABLESAMPLE

Specifies to use simple random sampling to return an approximate percentage of records. All rows in the total potential return set are equally eligible to be included in the sampling. Vertica performs this sampling before other filters in the query are applied. The number of records returned is not guaranteed to be the exact percentage of records defined by sampling-pct.

The TABLESAMPLE option is valid only with user-defined tables and Data Collector (DC) tables. Views and system tables are not supported.

sampling-pct

Specifies the percentage of records to be returned as a part of sampling. The value must be greater than 0 and less than 100.

ON

join-predicate

An equi-join based on one or more columns in the joined tables. invalid for NATURAL and CROSS joins, required for all other join types.

Alternative JOIN syntax options

Vertica supports two older join syntax conventions:

Table joins specified by join predicate in a WHERE clause
Table joins specified by a USING clause

For details, see Join Syntax.

Examples

The following SELECT statement qualifies its JOIN clause with the TABLESAMPLE option:

=> SELECT user_id.id, user_name.name FROM user_name TABLESAMPLE(50)
     JOIN user_id TABLESAMPLE(50) ON user_name.id = user_id.id;
  id  |  name
------+--------
  489 | Markus
 2234 | Cato
  763 | Pompey
(3 rows)

2 - Table-reference

A temporary name used for references to table.

Syntax

[[database.]schema.]table[ [AS] alias]

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
table: A table in the logical schema.
[AS] alias: A temporary name used for references to table.