FROM¶

Syntax¶

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

Where:

objectReference ::=
   {
      [<namespace>.]<object_name>
           [ AT | BEFORE ( <object_state> ) ]
           [ CHANGES ( <change_tracking_type> ) ]
           [ MATCH_RECOGNIZE ]
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
           [ SAMPLE ]
     | <table_function>
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
           [ SAMPLE ]
     | ( VALUES (...) )
           [ SAMPLE ]
     | [ LATERAL ] ( <subquery> )
           [ [ AS ] <alias_name> ]
     | @[<namespace>.]<stage_name>[/<path>]
           [ ( FILE_FORMAT => <format_name>, PATTERN => '<regex_pattern>' ) ]
           [ [AS] <alias_name> ]
     | DIRECTORY( @<stage_name> )
   }

Copy

Parameters¶

JOIN

Subclause that specifies to perform a join between two or more tables (or views or table functions). The join can be an inner join, outer join, or other type of join. The join can use the keyword JOIN or an alternative supported join syntax. For more details about joins, see JOIN and ASOF 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.

Alias names must follow the rules for Object identifiers.

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.

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');

Copy

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 |
+-------------+--------------+----------------+

Copy

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 |
+-------------+--------------+----------------+

Copy

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 |
+--------+

Copy

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

SELECT *
    FROM sales SAMPLE(10);

Copy

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

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

Copy

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);

Copy

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;

Copy

This example retrieves all metadata columns in a directory table for a stage named mystage:

SELECT * FROM DIRECTORY(@mystage);

Copy

This example retrieves the FILE_URL column values from a directory table for files greater than 100 K bytes in size:

SELECT FILE_URL FROM DIRECTORY(@mystage) WHERE SIZE > 100000;

Copy

This example retrieves the FILE_URL column values from a directory table for comma-separated value files:

SELECT FILE_URL FROM DIRECTORY(@mystage) WHERE RELATIVE_PATH LIKE '%.csv';

Copy