# SQL Expressions: WITH (Common Table Expressions)

Common Table Expressions (CTEs) are an alternative to subqueries. They are declared and used like this:
``````cte1 := sq.
Select(tbl.COLUMN_1).
From(tbl).
Where(tbl.COLUMN_2.Eq(tbl.COLUMN_3)).
CTE("cte1")
cte2 := sq.SelectAll().From(tbl).CTE("cte2")
sq.With(cte1, cte2).Select(cte1["column_1"]).From(cte1)
``````
Equivalent SQL:
``````WITH cte1 AS (
SELECT tbl.column_1
FROM tbl
WHERE tbl.column_2 = tbl.column3
), cte2 AS (
SELECT * FROM tbl
)
SELECT cte1.column1 FROM cte1
``````

### Recursive CTEs

This is how you define a recursive CTE:
``````-- SQL
WITH RECURSIVE tens (n) AS (
SELECT 10
UNION ALL
SELECT tens.n + 10 FROM tens WHERE tens.n + 10 <= 100
)
SELECT n FROM tens;
``````
``````// Go
tens := sq.RecursiveCTE("tens", "n") // name, followed by columns
tens = tens.
Initial(sq.Select(Int(10))).
UnionAll(sq.
Select(Fieldf("? + 10", tens["n"])). // [Fieldf defintion](/sq/basics/sql-escape-hatch.html#fieldf-and-predicatef)
From(tens).
Where(Fieldf("? + 10 <= 100", tens["n"]).Le(100)),
)
sq.With(tens).Select(tens["n"]).From(tens)
``````
You may omit the list of columns in the RecursiveCTE() defintion if your initial query already has column names:
``````-- SQL
WITH RECURSIVE subordinates AS (
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees AS e
WHERE employee_id = 2
UNION
SELECT e.employee_id, e.manager_id, e.full_name
FROM employees AS e
INNER JOIN subordinates ON subordinates.employee_id = e.manager_id
)
SELECT
subordinates.employee_id,
subordinates.manager_id,
subordinates.full_name
FROM
subordinates;
``````
``````// Go
e := tables.EMPLOYEES().As("e")
subordinates := RecursiveCTE("subordinates")
subordinates = subordinates.
Initial(
sq.Select(e.EMPLOYEE_ID, e.MANAGER_ID, e.FULL_NAME).
From(e).
Where(e.EMPLOYEE_ID.EqInt(2)),
).
Union(
sq.Select(e.EMPLOYEE_ID, e.MANAGER_ID, e.FULL_NAME).
From(e).
Join(subordinates, subordinates["employee_id"].Eq(e.MANAGER_ID)),
// This function is variadic. If you need to UNION any more queries,
// add them here. e.g.
// sq.Select(blabla).From(blabla).Where(condition1),
// sq.Select(blabla).From(blabla).Where(condition2),
)
sq.With(subordinates)
Select(
subordinates["employee_id"],
subordinates["manager_id"],
subordinates["full_name"],
).
From(subordinates)
``````

Just like with subqueries, if you reference a column that is not actually in the CTE you will get a `:blank:` in the generated SQL:
``````cte1 := sq.
Select(tbl.COLUMN_1).
From(tbl).
Where(tbl.COLUMN_2.Eq(tbl.COLUMN_3)).
CTE("cte1")
cte2 := sq.SelectAll().From(tbl).CTE("cte2")
sq.With(cte1, cte2).Select(cte1["column_xyz"]).From(cte1)
``````
Equivalent SQL:
``````WITH cte1 AS (
SELECT tbl.column_1
FROM tbl
WHERE tbl.column_2 = tbl.column3
), cte2 AS (
SELECT * FROM tbl
)
SELECT :blank: FROM cte1
``````

Both subqueries and CTEs are not type safe; their columns are only resolved at runtime. If you need type safe column references you should turn your subqueries and CTEs into SQL views instead, because table definitions can be generated from views.