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 ...
You can use
table_reference as any normal table in the external SELECT part. You can also use
WITH in sub queries.
WITH on the top level:
WITH t as (select a from t1 where b >= 'c') select * from t2,t where t2.c=t.a;
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);
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;