# `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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/values.md) 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)](/docs/data/data-cloud-query-guide/guide/query-joins.md).

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.

## Syntax

```sql
SELECT [ ALL | DISTINCT [ ON ( <expression> [, ...] ) ] ]
    [ FROM <from_item> [, ...] ]
    ...
```

`from_item` can be one of:

```sql
<table_name> [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]

<with_query_name> [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]

[ LATERAL ] ( <select> ) [ AS ] [<alias>] [ ( <column_alias> [, ...] ) ]

[ LATERAL ] <function_name> ( [ <argument> [, ...] ] )
    [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]

<from_item> [ NATURAL ] <join_type> <from_item>
    [ ON <join_condition> | USING ( <join_column> [, ...] ) ]
```

## Examples

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:

```sql
did | name
----+--------------
108 | Westward
111 | Walt Disney
112 | Warner Bros.
```

Actors:

```sql
id  | name
----+----------------
1   | Woody Allen
2   | Warren Beatty
3   | Walter Matthau
```

```sql
SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';
```

Results:

```sql
name
----
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody 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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/set-returning-functions.md) are supported, including [`result_scan`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/result-scan.md) for querying the results of a previously executed query.

```sql
SELECT * FROM generate_series(1, 5);
```

Results:

```sql
generate_series
---------------
              1
              2
              3
              4
              5
```