Complex SQL database queries often contain subqueries.
SELECT * FROM ...
WHERE name IN (SELECT name
FROM ...
WHERE ...)
These can quickly become unreadable, especially if multiple subqueries are involved. A nice way to organise such queries with multiple subqueries is called Common Table Expressions (CTE), or colloquially: “WITH queries”. They are supported by most contemporary SQL databases.
When using Common Table Expressions you list all the subqueries after a WITH keyword in front of the actual query and assign a name to each subquery:
WITH <subquery1_name> AS (SELECT ...), <subquery2_name> AS (SELECT ...), [ ... ] SELECT ... FROM ... [ ... ]
You can now refer to these subqueries in the main query by their names. They are conceptually just like temporary views. You can also refer to a subquery in the main query multiple times, whereas if the subquery was inlined you would have to repeat it. A subquery defined as a Common Table Expression can also refer to the preceding subqueries in the subquery list.
Recursion
A Common Table Expression can even refer to itself, which is a recursive definition. In some database systems you have to add the RECURSIVE keyword after WITH, in others you can leave it out. Here’s a recursive CTE that calculates factorial numbers:
WITH RECURSIVE factorials (n, fact) AS
(SELECT 0, 1
UNION ALL
SELECT n+1, (n+1)*fact FROM factorials
WHERE n < 6)
SELECT * FROM factorials;
N FACT 0 1 1 1 2 2 3 6 4 24 5 120
You could also use such a recursive query to descend into a tree hierarchy.
While the use cases for recursive queries are less frequent, I find the general concept of Common Table Expressions very useful to make complex queries more readable.
Schneide Blog 