Hello and welcome to the fourth part of our course. Today, we're going to introduce recursion and recursive CTEs, which is probably the most difficult aspect in our entire course. Don't worry if you can't understand something at first – take your time and read the instructions carefully again and again.
Recursive CTEs make it possible to process 'hierarchical structures' – trees and graphs – using SQL. For instance, such structures are used when talking about superior and inferior employees, railway connections, application menus, etc.
What we're going to show here is standard syntax, but you may expect some deviations in various databases. We're using Postgres, and the same syntax should work in SQL Server. Oracle supports recursive CTEs since version 11gR2. MySQL will support them from version 8.0. Some other databases use an alternative syntax with
CONNECT BY, but we won't cover it here.
If you're ready to take the challenge, let's move on.