1. SQL Reference Guide
  2. SQL Query Management
  3. SELECT Expression
  4. FROM Clause

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-5 Select all information for all the passenger records in the airline application

SELECT * FROM BaggageInfo

Explanation: In the above query, you use the FROM clause to retrieve all the passenger data from the BaggageInfo table.

Output: One sample row:
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
 [ {
    "id" : "79039899165297",
    "tagNum" : "17657806255240",
    "routing" : "MIA/LAX/MEL",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MEL",
    "flightLegs" : [ {
      "flightNo" : "BM604",
      "flightDate" : "2019-02-01T01:00:00",
      "fltRouteSrc" : "MIA",
      "fltRouteDest" : "LAX",
      "estimatedArrival" : "2019-02-01T03:00:00",
      "actions" : [ {
        "actionAt" : "MIA",
        "actionCode" : "ONLOAD to LAX",
        "actionTime" : "2019-02-01T01:13:00"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "BagTag Scan at MIA",
        "actionTime" : "2019-02-01T00:47:00"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "Checkin at MIA",
        "actionTime" : "2019-02-01T23:38:00"
      } ]
    }, {
      "flightNo" : "BM667",
      "flightDate" : "2019-01-31T22:13:00",
      "fltRouteSrc" : "LAX",
      "fltRouteDest" : "MEL",
      "estimatedArrival" : "2019-02-02T03:15:00",
      "actions" : [ {
        "actionAt" : "MEL",
        "actionCode" : "Offload to Carousel at MEL",
        "actionTime" : "2019-02-02T03:15:00"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "ONLOAD to MEL",
        "actionTime" : "2019-02-01T07:35:00"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "OFFLOAD from LAX",
        "actionTime" : "2019-02-01T07:18:00"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-02-02T03:13:00",
    "bagArrivalDate" : "2019.02.02T03:13:00"
  } ]

Example 6-6 Fetch the initial boarding station for all passengers from the airline application

SELECT DISTINCT
$bag.fullname,
$bag.contactPhone,
$flt_src as SOURCE
FROM BaggageInfo $bag, 
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src 
ORDER BY $bag.fullName

Explanation: The fltRouteSrc field in the bagInfo array of the BaggageInfo table includes the details of the source stations from where the passenger boards the flight. The first element in the flightLegs array holds the details of the initial travel leg.

In this query, you retrieve the first element from the flightLegs array, that is, $bag.bagInfo[].flightLegs[0].fltRouteSrc values to fetch the details of the initial stations. Notice that $bag is the alias for the BaggageInfo table and $flt_src is the alias for $bag.bagInfo.flightLegs[0].fltRouteSrc field. You can alias the field names in the FROM clause and use them for the rest of the SELECT expression.

You use the ORDER BY clause to reorder the sequence of rows in the ascending order of passenger names.

Note:

In this query, you get as many entries as the total number of bags. If a passenger travels with two bags, the output displays two entries. To avoid this, you can use the DISTINCT keyword in the SELECT statement. The query then returns only one copy of each set of duplicate rows selected.
Output: 
{"fullname":"Adam Phillips","contactPhone":"893-324-1064","SOURCE":"MIA"}
{"fullname":"Adelaide Willard","contactPhone":"421-272-8082","SOURCE":"GRU"}
{"fullname":"Dierdre Amador","contactPhone":"165-742-5715","SOURCE":"JFK"}
{"fullname":"Doris Martin","contactPhone":"289-564-3497","SOURCE":"BZN"}
{"fullname":"Elane Lemons","contactPhone":"600-918-8404","SOURCE":"MXP"}
{"fullname":"Fallon Clements","contactPhone":"849-731-1334","SOURCE":"MXP"}
{"fullname":"Gerard Greene","contactPhone":"395-837-3772","SOURCE":"SFO"}
{"fullname":"Henry Jenkins","contactPhone":"960-428-3843","SOURCE":"SFO"}
{"fullname":"Joanne Diaz","contactPhone":"334-679-5105","SOURCE":"MIA"}
{"fullname":"Kendal Biddle","contactPhone":"619-956-8760","SOURCE":"JFK"}
{"fullname":"Lisbeth Wampler","contactPhone":"796-709-9501","SOURCE":"LAX"}
{"fullname":"Lorenzo Phil","contactPhone":"364-610-4444","SOURCE":"SFO"}
{"fullname":"Lucinda Beckman","contactPhone":"364-610-4444","SOURCE":"SFO"}
{"fullname":"Mary Watson","contactPhone":"131-183-0560","SOURCE":"YYZ"}
{"fullname":"Michelle Payne","contactPhone":"575-781-6240","SOURCE":"SFO"}
{"fullname":"Omar Harvey","contactPhone":"978-191-8550","SOURCE":"MEL"}
{"fullname":"Raymond Griffin","contactPhone":"567-710-9972","SOURCE":"MSQ"}
{"fullname":"Rosalia Triplett","contactPhone":"368-769-5636","SOURCE":"JFK"}
{"fullname":"Teena Colley","contactPhone":"539-097-5220","SOURCE":"MSQ"}
{"fullname":"Zina Christenson","contactPhone":"987-210-3029","SOURCE":"MIA"}
{"fullname":"Zulema Martindale","contactPhone":"666-302-0028","SOURCE":"MIA"}