I believe the author's point is that using these recursive SQL queries is more efficient, as the DB engine is inherently faster than your (presumably) scripting language. So this solution is for when the in-memory method is failing to scale.
Not saying necessarily that the DB engine is less efficient, but rather that it is serves as a limiting factor and anything you can offload onto other servers is a good solution (for highly-visited websites).
Is the DB engine really inherently faster than the scripting language?
As for anything, the answer is "it depends". It depends on what you want to do. In this case that is grab all rows in a specific dataset.
If you in any case want to grab the dataset X, consisting of a given set of rows, asking the database to figure how to fetch all rows in one operation as efficiently as possible will (or at least should on any respectable database engine) be more efficient than having the scripting language sending multiple requests which can only be optimized one by one.
For reference, I've only tried recursive queries in Microsoft SQL Server, but they are pretty fucking neat from a geeky perspective.
Yeah, I was about to mention that MSSQL has had this 'feature' for years. I've used it a lot for retrieving organizational structures from the typical parent-child table structure.
The best part of it, is that it is MUCH simpler than either trying to roll your own through cursors or dealing with it in whatever language outside the database.
I believe the author's point is that using these recursive SQL queries is more efficient, as the DB engine is inherently faster than your (presumably) scripting language. So this solution is for when the in-memory method is failing to scale.