# with {#with}

The `with` clause \(subquery factoring\) is resolved as a temporary table to store the intermediate results of subqueries for further reference.

Using `with` clause has the following advantages:

-   It simplifies complex SQL queries and improves readability.

-   Repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being re-queried by each reference.

-   It reduces the memory overhead of variable definitions as the temporary table is released after query execution.


**Parent topic:**[SQL Reference](../../Programming/SQLStatements/SQLStatement.md)

## Syntax {#syntax}

```python
with table_name_1[(colNames..)] as (select_statement_1),
table_name_2[(colNames..)] as (select_statement_2),
...
table_name_n[(colNames..)] as (select_statement_n)
final_select_statement
```

Note:

-   The subquery after with cannot end with a semicolon \(";"\).

-   Separate multiple subqueries with a comma \(","\). Do not end the last subquery with ",".

-   `with as` is not supported in user-defined functions.

-   `with` supports distributed queries to access data from DFS tables.


## Arguments {#arguments}

**table\_name** is the name of temporary table.

**colNames..** are variables used to rename the columns returned by `as` clause. The number of *colNames* must be the same as the number of returned columns.

**select\_statement** can be `select` or `exec` statement.

## Examples {#examples}

```python
t1 = table(1 3 4 5 8 as id, 2 2.5 2.4 2.2 2.9 as val)
t2 = table(1 2 4 6 8 as id, `a`a`b`d`c as sym)

with tmp as (select * from t1 inner join t2 on t1.id=t2.id) select count(*) from tmp
3
```

