You can define multiple queries with a single SELECT statement by specifying the queries by name.
The WITH modifier defines a named query list from which the SELECT statement can select data. In this way, a query in the WITH modifier is similar to a derived table. Named queries are also referred to as common table expressions (CTEs).
The WITH modifier is ANSI SQL:2011 compliant.
Other SQL dialects support similar non-ANSI standard statements with names such as CONNECT BY PRIOR.