FROM clause

The FROM clause specifies the source of the data that the rest of the query should work with. Logically, the FROM clause is the place where the execution of the query begins.

A FROM clause can contain a single table, a combination of multiple tables joined using the JOIN clause, or another SELECT query in a subquery node.

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> ]
     | <table_function>
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
     | ( VALUES (...) )
     | ( <subquery> )
           [ [ AS ] <alias_name> ]
     | DIRECTORY( @<stage_name> )
   }

Parameters

  • [<namespace>.]<object_name>
    Specifies the name of the object (table or view) being queried.

  • <table_function>
    Specifies the system table function, UDF table function, or class method to call in the FROM clause.

  • VALUES
    The VALUES clause may contain literal values or expressions to be used in the FROM clause. This expression may also contain table and column aliases (not shown in the diagram above).

  • <subquery>
    Subquery in the FROM clause.

  • DIRECTORY( @stage_name )
    Specifies the name of the stage that includes the directory table.

  • [ AS ] <alias_name>
    Indicates the name given for the object to which it refers. Can be used with any other subqueries in a FROM clause. The AS operator may be omitted.

  • JOIN
    Indicates the execution of a join between two (or more) tables (or views or table functions). The join can be internal external or of another type. The join can use the JOIN keyword or an alternative supported join syntax.
    For a detailed description, see JOIN clause.

Examples

  • Select all columns from the table named my_table:

    SELECT *
FROM my_table;

  • Select all columns from a table named my_table through the alias mt:

    SELECT mt.*
FROM my_table mt;

  • Using the prefix alias:

    SELECT mt.*
FROM mt: my_table;

  • Select all columns from the my_table table in the my_schema schema:

    SELECT *
FROM my_schema.my_table;

  • Select column i from table function range, where the first column of the range function is renamed to i:

    SELECT t.i
FROM range(1000) AS t(i);

  • Select all columns from the subquery:

    SELECT *
FROM (SELECT * FROM my_table);

  • Merge the two tables:

    SELECT *
FROM my_table
JOIN other_table
    ON my_table.key = other_table.key;

    For a detailed description, see JOIN clause.