WITH and WITH RECURSIVE

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 ...

Get Mastering The Faster Web with PHP, MySQL, and JavaScript now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.