FROM Clause

The FROM clause is used to retrieve rows from the referenced table(s).

Syntax

from_clause ::= FROM (single_from_table | nested_tables | left_outer_join_tables | unnest_syntax)

single_from_table ::= aliased_table_name

aliased_table_name ::= 
   (table_name | SYSTEM_TABLE_NAME) [[AS] table_alias]

table_alias ::= [$] id

Semantics

As shown in the syntax, the FROM clause can either reference a single table, or include a nested table clause or a left outer join clause. It can also include an unnest syntax. For nested tables, see the Using NESTED TABLES clause to query multiple tables in the same hierarchy section. To learn more about left outer joins, see Left Outer Join (LOJ).

unnest_syntax

You can use unnest_syntax to unnest one or more arrays or maps, that is to convert the arrays or maps into a set of rows. To understand how unnest_syntax is used in queries, see Unnest Arrays & Maps.

single_from_table

In a simple FROM clause, the table is specified by its name, which may be a composite (dot-separated) name in the case of child tables. The result of the simple FROM clause is a sequence containing the rows of the referenced table.

aliased_table_name

The table name may be followed by a table alias. Table aliases are essentially variables ranging over the rows of the specified table. If no alias is specified, one is created internally, using the name of the table as it is spelled in the query, but with dot chars replaced with '_' in the case of child tables. See Table Hierarchies.

Note:

Table aliases are case-sensitive, like variable names.

The other clauses of the SELECT expression operate on the rows produced by the FROM clause, processing one row at a time. The row currently being processed is called the context row. The columns of the context row can be referenced in expressions either directly by their names or by the table alias followed by a dot char and the column name. See the Column References section. If the table alias starts with a dollar sign ($), then it actually serves as a variable declaration for a variable whose name is the alias. This variable is bound to the context row as a whole and can be referenced within sub expressions of the SELECT expression. For example, it can be passed as an argument to the expiration_time function to get the expiration time of the context row. See the expiration_time function function. In other words, a table alias like $foo is an expression by itself, whereas foo is not. Notice that if this variable has the same name as an external variable, it hides the external variable. This is because the FROM clause creates a nested scope, which exists for the rest of the SELECT expression.

Example 6-2 From Clause

SELECT * FROM users;

The above example selects all information for all users.