Summary: The WITH Clause in SQL allows for Common Table Expressions (CTEs) which act as temporary views within a single SQL statement. There are two types of CTEs: ordinary and recursive. Ordinary CTEs simplify queries, while recursive CTEs enable hierarchical and recursive queries not possible in standard SQL. A recursive CTE must follow specific syntax and guidelines, allowing for complex recursive queries. Examples are provided for how to use recursive CTEs to query trees, graphs, organizational structures, family trees, and even generate ASCII art of the Mandelbrot Set. The ORDER BY clause within a recursive query can control whether the search is depth-first or breadth-first.
https://www.sqlite.org/lang_with.html#outlandish_recursive_query_examples