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 theSELECT
command. For example,FROM foo AS f
, the rest of theSELECT
tof
notfoo
. 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 mainSELECT
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 theSELECT
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 callFROM
item. In Data Cloud SQL, subqueries can always access the attributes of precedingFROM
items, even if theLATERAL
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 useREPEATABLE
, 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
andSYSTEM
.The
BERNOULLI
method scans the whole table and selects or ignores individual rows independently.The
SYSTEM
method does block-level sampling. TheSYSTEM
method is faster than theBERNOULLI
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 theROWS
syntax, or provide a percentage of rows by using thePERCENT
syntax. Use a numeric literal for the<sample_size>
. Data Cloud SQL usesPERCENT
by default.Optionally, you can use the
BETWEEN
clause withPERCENT
sample size to define the minimum and maximum number of rows to return. Use theUNBOUNDED
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 the INNER
and OUTER
join types, specify the NATURAL
, ON join_condition
, or
USING (join_column [, ...])
joins conditions.
<join_type>
CROSS JOIN
andINNER 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. TheJOIN
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 aboolean
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 aUSING
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.