WITH keyword signifies a Common Table Expression (CTE). It allows you to refer to a subquery expression many times in a query, as if having a temporary table that only exists for the duration of a query.
There are two kinds of CTEs:
WITH [RECURSIVE] table_reference as (SELECT ...) SELECT ...
Below is an example with the
WITH at the top level:
WITH t AS (SELECT a FROM t1 WHERE b >= 'c') SELECT * FROM t2, t WHERE t2.c = t.a;
The example below uses
WITH in a subquery:
SELECT t1.a, t1.b FROM t1, t2 WHERE t1.a > t2.c AND t2.c IN(WITH t AS (SELECT * FROM t1 WHERE t1.a < 5) SELECT t2.c FROM t2, t WHERE t2.c = t.a);
Below is an example of a Recursive CTE:
WITH RECURSIVE ancestors AS ( SELECT * FROM folks WHERE name="Alex" UNION SELECT f.* FROM folks AS f, ancestors AS a WHERE f.id = a.father OR f.id = a.mother ) SELECT * FROM ancestors;