FROM Clause

The FROM clause defines one or more source tables as the data source. If multiple sources are specified, the result is the join of the sources. Use the FROM clause with the WHERE clause to reduce the number of returned values in the join.

The FROM clause can contain these elements.

<table_name>

The name of an existing table or view.

<alias>

A substitute name for the FROM item. When you provide an alias, the alias replaces the name for the SELECT command. For example, FROM foo AS f, the rest of the SELECT to f not foo. If you provide an alias, you can also create a column alias list for one or more columns of the table.

<select>

The <select> is a subquery of the main SELECT command. Surround the subquery in parentheses. If you don’t provide an alias, the columns of the subquery are only accessible if their names are unique. You can use VALUES as subquery.

<with_query_name>

You can reference a WITH query by its name. You can also provide an alias in the same way.

<function_name>

Function calls can appear in the FROM clause. The function’s output is created as a temporary table for the duration of the SELECT command. You can provide a function alias and column alias list for one or more attributes of the function’s composite return type.

LATERAL

The LATERAL keyword can precede a subquery or a function call FROM item. In Data Cloud SQL, subqueries can always access the attributes of preceding FROM items, even if the LATERAL keyword isn’t specified.

Sampling

TABLESAMPLE sampling_method ( sample_size ) [ REPEATABLE ( seed ) ]

A TABLESAMPLE clause after a <table_name> specifies the <sampling_method> to use for retrieving a subset of the rows in that table. Table sampling occurs before other filters.

The optional REPEATABLE clause specifies a <seed> number to use for generating random numbers within the sampling method. The seed value can be any non-null floating-point literal. Two queries that specify the same seed and <sample_size> values return the same sample of the table. Different seed values usually produce different samples. If you don’t use REPEATABLE, a new random sample is selected for each query based on a system-generated seed.

<sampling_method>

Data Cloud SQL supports two sampling methods, BERNOULLI and SYSTEM.

The BERNOULLI method scans the whole table and selects or ignores individual rows independently.

The SYSTEM method does block-level sampling. The SYSTEM method is faster than the BERNOULLI method for small sampling percentages, but it can return a less random sample.

<sample_size>

The <sample_size> argument determines the number of rows to be sampled from the table. Provide a fixed number of rows by using the ROWS syntax, or provide a percentage of rows by using the PERCENT syntax. Use a numeric literal for the <sample_size>. Data Cloud SQL uses PERCENT by default.

Optionally, you can use the BETWEEN clause with PERCENT sample size to define the minimum and maximum number of rows to return. Use the UNBOUNDED keyword to leave the minimum or maximum undefined.

The lower bound isn’t guaranteed. If the table has too few rows, the sample can be smaller than the given lower bound of PERCENT or the given row count of ROWS.

Joins

A JOIN clause combines two FROM items. Use parentheses to determine the order of nesting. In the absence of parentheses, JOIN nests left to right. JOIN binds more tightly than the commas separating FROM-list items. For examples of joins, see Join Records from Different DMOs and DLOs.

For the INNER and OUTER join types, specify the NATURAL, ON join_condition, or USING (join_column [, ...]) joins conditions.

<join_type>

CROSS JOIN and INNER JOIN return all of the results of the provided items.

LEFT OUTER JOIN returns all combined rows that pass its join condition and each row in the left-hand table. This join inserts null values in the left-hand row for the right-hand unmatched columns. The JOIN clause’s condition is considered to match rows. Outer conditions are applied after the join.

RIGHT OUTER JOIN returns all combined rows that pass its join condition and each row in the right-hand table. This join inserts null values in the right-hand row for the left-hand unmatched columns.

FULL OUTER JOIN returns all the joined rows, the unmatched left-hand rows (extended with nulls on the right), and the unmatched right-hand rows (extended with nulls on the left).

ON <join_condition>

ON <join_condition> is an expression that results in a boolean that specifies the rows in a join that match.

USING ( <join_column> [, ...] )

One of each pair of equivalent columns is included in the join output.

NATURAL

NATURAL is a USING list with all columns in the two tables that have matching names. If there are no common column names, NATURAL returns all items.

from_item can be one of:

sample_size can be one of:

This example shows how to obtain the union of the tables distributors and actors, limiting the results to those that begin with the letter W in each table.

This example shows how to use a function in the FROM clause. Data Cloud SQL doesn’t support user-defined table functions, but some built-in table functions are supported.