Course of the Month: Recursive Queries

Recursive Queries is one of SQL’s more advanced features. You could say they divide SQL users into beginners and experts. But how do you learn recursive queries? And why do you need them, anyway? Agnieszka Kozubek-Krycuń, Chief Content Officer at Vertabelo, is here to help. In this article, she answers your questions about our August SQL Course of the Month: Recursive Queries.

Recursive queries … that sounds very serious. What are they?

Our Recursive Queries course is actually about something called Common Table Expressions. A Common Table Expression (CTE) is a feature that makes long and complex SQL queries more readable. It allows you to define a temporary named result set that you can use in the rest of your query. Common Table Expressions are often called WITH queries because they are introduced with the WITH keyword.

So CTEs help me clean up my queries?

Yes, but they can do much more. Common Table Expressions let you define an intermediary result set that you can refer to later in the query. Now you can prepare complex reports that require multiple levels of aggregation with a lot of intermediate steps. For a more detailed explanation, see the articles How to Organize SQL Queries with CTEs, Simplify SQL Code: Recursive Queries in DBMS, and Subquery vs. CTE: A SQL Primer. My colleagues give some interesting examples of using CTEs.

Recursive queries are the most spectacular usage of CTEs. This is why we named our course Recursive Queries, to advertise the toughest topic discussed in the course.

What else can these magical CTEs do?

Recursive queries let you analyze hierarchical data in SQL, such as trees or graphs. What are those? Think about a company and its employees. Each regular employee has a manager. This manager also has a manager. And that manager can have a manager, and so on. This employee hierarchy goes all the way up to the CEO.

Recursive queries let you analyze these structures. For example, if you wanted to list the chain of supervisors for each employee, you’d use a recursive CTE. If you wanted to list all direct and indirect subordinates of a certain manager, you’d use a recursive CTE. The same thing will happen if you want to find out what level a certain person is. A CTE will get you the answer!

Other examples of hierarchical structures include:

• Files and directories in a file system.
• Product categories and subcategories in a store.
• Road or tram connections that form a graph.

All of these, and lots more, can be analyzed with recursive queries.

Are CTEs new to SQL?

Common Table Expressions were first introduced in the SQL:1999 standard, but the different dialects of SQL incorporated them at different times. For SQL Server, they became available in 2005. PostgreSQL made CTEs available starting with version 8.4, or 2009. MySQL waited a little bit longer and made CTEs available in 2018, in version 8.0. So this feature is fairly new in SQL, but it’s very useful.

You can learn all this at LearnSQL.com. Do other courses include CTEs?

The WITH syntax is usually not taught in a typical SQL course. I don’t know of any other interactive SQL courses that teach CTEs or recursive SQL queries.

What topics does this course cover? What will I learn?

We start with simple Common Table Expressions, just to get to know the WITH syntax. Next, we show how you can nest CTEs, or use one CTE inside another. With nested CTEs, you can create complex reports and compute statistics using multi-level aggregations. We also demonstrate how CTEs can be used to organize long queries.

Then we go to the creme de la creme of this SQL course: recursive queries. We teach you how to write them, step by step. We want to make sure that, after completing the course, you’ll be able to write recursive queries on your own. There are lots of examples and you can practice writing recursive queries on different data sets. This is the most challenging part of the entire course, maybe even in all our SQL courses.

Finally, we show how the WITH syntax can be used in INSERT, UPDATE, and DELETE statements.

Is the SQL Recursive Queries course part of a larger track?

It’s part of our Advanced SQL track for users who know the basics of SQL and want to deepen their knowledge. It’s also included in our complete SQL from A to Z track. This track is for complete beginners and its goal is to teach you all the SQL that you’ll ever need.

Who should take the Recursive Queries course?

Knowing CTEs is essential for anyone who prepares long and complex reports in SQL: data analysts, financial analysts, marketing analysts, managers, etc. IT professionals – programmers, software engineers, DBAs, etc. – could use it as well.

How do platform users rate this SQL course?

Currently, the rating is 5 stars out of 5. But I warn you, this course is challenging!

Will I receive a certificate to post on my LinkedIn profile?

Of course. As soon as you finish all of the exercises, you’ll get a certificate. We encourage our users to attach such certificates to their CVs and LinkedIn profiles. Here's a guide on how to do it.

It is also worth showing off your new skills on other social media platforms. You never know, maybe your boss will see it and give you a raise. Or maybe some recruiters will notice you and call you with an interesting job offer!

You are Vertabelo’s Chief Content Officer. This means you’re involved in the creation of all the LearnSQL.com courses. What do you remember about working on this course?

I remember being surprised by the images of the Romanesco broccoli and Russian dolls that Adrian, one of the course authors, used to illustrate the concept of recursion.

To launch this course, we needed to introduce a number of changes in our platform. We had to add support for WITH queries. We also added some safety mechanisms to make sure the user query does not go forever, which can happen with incorrectly written recursive queries.

This course was originally called “Common Table Expressions” but we later decided to rename it “Recursive Queries” to showcase the toughest thing you can learn in this course.

Now I think that perhaps we should split this SQL course into two courses: one for non-recursive Common Table Expressions (used to organize long queries) and the other just for recursive queries. What do you guys think about this idea? Let us know in the comments.