FROM Clause

The FROM clause defines one or more source tables as the data source. If multiple sources are specified, the result is the join of the sources. Use the FROM clause with the WHERE clause to reduce the number of returned values in the join.

The FROM clause can contain these elements.

<table_name>

The name of an existing table or view.

<alias>

A substitute name for the FROM item. When you provide an alias, the alias replaces the name for the SELECT command. For example, FROM foo AS f, the rest of the SELECT to f not foo. The AS keyword is optional in Data 360 SQL. You can write FROM foo f instead. If you provide an alias, you can also create a column alias list for one or more columns of the table.

<select>

The <select> is a subquery of the main SELECT command. Surround the subquery in parentheses. If you don’t provide an alias, the columns of the subquery are only accessible if their names are unique. You can use VALUES as subquery.

<with_query_name>

You can reference a WITH query by its name. You can also provide an alias in the same way.

<function_name>

Function calls can appear in the FROM clause. The function’s output is created as a temporary table for the duration of the SELECT command. You can provide a function alias and column alias list for one or more attributes of the function’s composite return type.

LATERAL

The LATERAL keyword can precede a subquery or a function call FROM item. In Data 360 SQL, subqueries can always access the attributes of preceding FROM items, even if the LATERAL keyword isn't specified.

Joins

A JOIN clause combines two FROM items. Use parentheses to determine the order of nesting. In the absence of parentheses, JOIN nests left to right. JOIN binds more tightly than the commas separating FROM-list items. For examples of joins, see Join Records from Different Data Model Objects (DMOs) and Data Lake Objects (DLOs).

For the INNER and OUTER join types, specify the NATURAL, ON join_condition, or USING (join_column [, ...]) joins conditions.

<join_type>

CROSS JOIN and INNER JOIN return all of the results of the provided items.

LEFT OUTER JOIN returns all combined rows that pass its join condition and each row in the left-hand table. This join inserts null values in the left-hand row for the right-hand unmatched columns. The JOIN clause’s condition is considered to match rows. Outer conditions are applied after the join.

RIGHT OUTER JOIN returns all combined rows that pass its join condition and each row in the right-hand table. This join inserts null values in the right-hand row for the left-hand unmatched columns.

FULL OUTER JOIN returns all the joined rows, the unmatched left-hand rows (extended with nulls on the right), and the unmatched right-hand rows (extended with nulls on the left).

ON <join_condition>

ON <join_condition> is an expression that results in a boolean that specifies the rows in a join that match.

USING ( <join_column> [, ...] )

One of each pair of equivalent columns is included in the join output.

NATURAL

NATURAL is a USING list with all columns in the two tables that have matching names. If there are no common column names, NATURAL returns all items.

1SELECT [ ALL | DISTINCT [ ON ( <expression> [, ...] ) ] ]
2    [ FROM <from_item> [, ...] ]
3    ...

from_item can be one of:

1<table_name> [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
2
3<with_query_name> [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
4
5[ LATERAL ] ( <select> ) [ AS ] [<alias>] [ ( <column_alias> [, ...] ) ]
6
7[ LATERAL ] <function_name> ( [ <argument> [, ...] ] )
8    [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
9
10<from_item> [ NATURAL ] <join_type> <from_item>
11    [ ON <join_condition> | USING ( <join_column> [, ...] ) ]

This example shows how to obtain the union of the tables distributors and actors, limiting the results to those that begin with the letter W in each table.

Distributors:

1did | name
2----+--------------
3108 | Westward
4111 | Walt Disney
5112 | Warner Bros.

Actors:

1id  | name
2----+----------------
31   | Woody Allen
42   | Warren Beatty
53   | Walter Matthau

1SELECT distributors.name
2    FROM distributors
3    WHERE distributors.name LIKE 'W%'
4UNION
5SELECT actors.name
6    FROM actors
7    WHERE actors.name LIKE 'W%';

Results:

1name
2----
3Walt Disney
4Walter Matthau
5Warner Bros.
6Warren Beatty
7Westward
8Woody Allen

This example shows how to use a function in the FROM clause. Data 360 SQL doesn’t support user-defined table functions, but some built-in set-returning functions are supported, including result_scan for querying the results of a previously executed query.

1SELECT * FROM GENERATE_SERIES(1, 5);

Results:

1generate_series
2---------------
3              1
4              2
5              3
6              4
7              5