The first feature is what is known as a Common Table Expression (CTE). A CTE is a temporary result set which allows you to join the same data to itself multiple times. There are two types of CTEs: non-recursive (WITH) and recursive (WITH RECURSIVE).
The non-recursive kind of CTE works like a derived table, allowing you to SELECT from a temporary result set. A simple example, using a fictitious staff table, would be:
WITH accountants AS ( SELECT id, first_name, last_name FROM staff WHERE dept = 'accounting')SELECT id, first_name, last_nameFROM accountants;
The recursive kind of CTE is composed of two parts. The first part of the query is what is called the anchor member of the CTE. The anchor's result set is what is ...