Categories:
Query Syntax

FROM

Specifies the tables, views or table functions to use in a SELECT statement.

See also:
AT | BEFORE , JOIN , PIVOT , SAMPLE / TABLESAMPLE , UNPIVOT

Syntax

SELECT ...
FROM objectReference [ JOIN objectReference [ ... ] ]
[ ... ]

Where:

objectReference ::=
   {
      [<namespace>.]<object_name>
           [ AT | BEFORE ( <object_state> ) ]
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
           [ SAMPLE ]
     | <table_function>
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
           [ SAMPLE ]
     | ( VALUES (...) )
           [ SAMPLE ]
     | [ LATERAL ] ( <query> )
           [ [ AS ] <alias_name> ]
     | @[<namespace>.]<stage_name>[/<path>]
           [ ( FILE_FORMAT => <format_name>, PATTERN => '<regex_pattern>' ) ]
           [ [AS] <alias_name> ]
   }
JOIN
Subclause that specifies to perform a join between two or more tables (or views or table functions). For more details, see JOIN.
[ AS ] alias_name
Specifies a name given to the object reference it is attached to. Can be used with any of the other subclauses in the FROM clause.
VALUES
The VALUES clause can specify literal values or expressions to be used in the FROM clause. This clause can contain table and column aliases (not shown in the diagram above). For more details about the VALUES clause, see VALUES.

Object or Table Function Clause

[namespace.]object_name

Specifies the name of the object (table or view) being queried.

The object name can be qualified using namespace (in the form of db_name.schema_name.object_name or schema_name.object_name). A namespace is not required if the context can be derived from the current database and schema for the session.

When specifying a table/view name to query, you can also specify the following optional subclause:

AT | BEFORE ( object_state )
Optional subclause that specifies the time-based or event-based historical state of the table or view for Time Travel. For more details, see AT | BEFORE.
table_function
Specifies either a system-defined or UDF table function to call within the FROM clause. For more details, see Table Functions or UDFs (User-Defined Functions).
PIVOT | UNPIVOT
Optional subclause that specifies to pivot or unpivot the results of the FROM clause. For more details, see PIVOT and UNPIVOT.
SAMPLE
Optional subclause that specifies to sample rows from the table/view. For more details, see SAMPLE / TABLESAMPLE.

Inline View Clause

[ LATERAL ] ( query )
Specifies an inline view within the FROM clause. If the optional LATERAL keyword is used, then the view can refer to columns from other tables (or views or table functions) in the current FROM clause, to the left of the inline view.

Staged File Clause

@[namespace.]stage_name[/path]

Specifies a named stage to be queried (or ~ for referring to the stage for the current user or % followed by a table name for referring to the stage for the specified table).

When querying a stage, you can also optionally specify a named file format and pattern:

( FILE_FORMAT => format_name [ , PATTERN => 'regex_pattern' ] )
Specifies a named file format object to use for the stage and a pattern to filter the set of files in the stage.

For more details about querying stages, see Querying Data in Staged Files.

Usage Notes

  • Object names are SQL identifiers. They are case-insensitive by default. To preserve case, enclose them between double quotes (" ").

Examples

Create a table and load data into it:

CREATE TABLE ftable1 (retail_price FLOAT, wholesale_cost FLOAT, description VARCHAR);
INSERT INTO ftable1 (retail_price, wholesale_cost, description) 
  VALUES (14.00, 6.00, 'bling');

Here is a basic example of using the FROM clause:

SELECT description, retail_price, wholesale_cost 
    FROM ftable1;
+-------------+--------------+----------------+
| DESCRIPTION | RETAIL_PRICE | WHOLESALE_COST |
|-------------+--------------+----------------|
| bling       |           14 |              6 |
+-------------+--------------+----------------+

This example is identical to the previous example, but specifies the table name qualified by the schema for the table:

SELECT description, retail_price, wholesale_cost 
    FROM temporary_doc_test.ftable1;
+-------------+--------------+----------------+
| DESCRIPTION | RETAIL_PRICE | WHOLESALE_COST |
|-------------+--------------+----------------|
| bling       |           14 |              6 |
+-------------+--------------+----------------+

This example creates an inline view and then uses it in the query:

SELECT v.profit 
    FROM (SELECT retail_price - wholesale_cost AS profit FROM ftable1) AS v;
+--------+
| PROFIT |
|--------|
|      8 |
+--------+

This example queries a sample of 10% of the data in the table:

SELECT *
    FROM sales SAMPLE(10);

This example executes a UDTF (user-defined table function):

SELECT *
    FROM TABLE(Fibonacci_Sequence_UDTF(6.0::FLOAT));

These examples use an AT clause to return historical data from the following specified points in the past:

  • One day earlier than the current time (-86400 = -3600 * 24).
  • Specific time and day.
SELECT *
    FROM sales AT(OFFSET => -86400);
SELECT *
    FROM sales AT(TIMESTAMP => '2018-07-27 12:00:00'::TIMESTAMP);

For more details about AT, see AT | BEFORE.

This example queries files located in a named stage:

SELECT
    v.$1, v.$2, ...
  FROM
    @my_stage( FILE_FORMAT => 'csv_format', PATTERN => '.*my_pattern.*') v;