Recursive WITH clauses pack a powerful punch. They are the only SQL construct that allow for recursive processing without resorting to loops or other imperative constructs. In this video, learn how they are processed and explore a few interesting use cases such as traversing a URL list.
- [Instructor] So far we've used many WITH clauses. … They helped us break down complex tasks, shorten queries, … but there's much more to them than that. … WITH clauses pack a powerful and unique ability … to process recursions without resorting to any type … of imperative solutions. … To create a recursive WITH clause, … we must specify our intent … using the WITH recursive keyword, … the recursive query definition consists of two parts. … The first is a standalone, … ordinary select query called the anchor. … The anchor defines the initial set, … the starting point for the recursion. … The anchor query is followed by either a union all … or a union distinct set operator, … which in turn is followed by the recursive query itself. … The recursion is indicated … by referencing the WITH clause name within the query. … This may seem counterintuitive at first, as typically, … we cannot reference an object within its own definition. … As for the processing, first, the anchor query is evaluated. …
This course was created by Ami Levin. We are pleased to offer this training in our library.