The 7 Best Articles about the SQL Recursive Query

Do you want to learn about recursive queries but don’t know where to start? Here’s our selection of seven articles that should help you with diving into the recursive queries world.

I won’t lie to you. Learning and understanding recursive queries can be difficult. But difficult things are usually worth trying. And by learning recursive queries, you’re making sure you know how to work with this very powerful tool.

It takes time and some hard work to learn recursive queries. It also takes some really good articles to help you understand the concepts behind recursive queries, what they do, and how they do it.

Here are seven articles I find very well written. They give you enough info to slowly start introducing recursive queries into your SQL vocabulary.

1. Recursive CTEs Explained

Author: Kris Wenzel

Link: https://www.essentialsql.com/recursive-ctes-explained/

Description: This is an article published on the Essential SQL site. Their blog is dedicated to quality articles about SQL and databases. This article explains everything from recursion in general to recursive query syntax, its parts, to recursive query examples with codes and query results.

Why it’s useful: It is expertly written in terms of technical knowledge and presentation. It’s great for beginners to learn the terminology and concepts both in theory and in practice, but even advanced users wanting to fill in some gaps will find it helpful. Here, you get everything you need to know to start using recursive CTEs.

2. Recursion in SQL Explained Visually

Author: Denis Lukichev

Link: https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b

Description: This is another very helpful article if you want to learn about recursive queries in SQL. It’s published on Medium within the “Start it up” blog. As the title of the article says, its focus is on visually explaining recursion in SQL. And it does that! Using very nice drawings and charts, the author explains how recursion works in general, what happens when you run a recursive query, and what every part of a recursive CTE does. Everything’s explained in just one recursive query example, but having plenty of examples is not the purpose of this article.

Why it’s useful: It is simple, yet it explains well how recursive queries work. That’s not easy to achieve. It is visually nicely presented. I’m sure you’ll easily remember the concepts presented here and apply them to some examples of your own or from other articles.

3. Get to Know the Power of SQL Recursive Queries

Author: Michał Kołodziejski

Link: https://learnsql.com/blog/get-to-know-the-power-of-sql-recursive-queries/

Description: This one is published on LearnSQL.com’s blog, where you can find other interesting and useful articles as well as SQL courses including the Recursive Queries course. I’ve already mentioned how powerful recursive queries are; when you read this article, you’ll understand why. You’ll see what problems in SQL call for using recursive CTEs. It also dives very deep into the syntax. After reading this article, it’ll be almost impossible to not know how to write a recursive query.

Why it’s useful: A little more technical than the previous two articles, but I think you are ready for that now. It explains the recursive query syntax very thoroughly with plenty of examples.

4. Simplifying Recursive SQL Queries

Author: Andy Kappen

Link: https://haughtcodeworks.com/blog/software-development/recursive-sql-queries-using-ctes/

Description: Haught Codeworks was a web development company from Colorado. Although they no longer do client work, their blog is still a source of some great articles. For example, this one focuses on simplifying querying self-referential tables, with a recursive query being the perfect tool for the job. It shows you the recursive query syntax. But its main strength is the several recursive query examples you can use to practice. This should help you in understanding when recursive queries are useful.

Why it’s useful: It is a rather focused and short article. It does not try to explain everything. If you want to see several examples of recursive queries and learn the syntax along the way, this article’s for you.

5. Common Table Expressions (Introduction to CTEs)

Author: Kris Wenzel

Link: https://www.essentialsql.com/introduction-common-table-expressions-ctes/

Description: Here’s another article I found on Essential SQL. This one focuses more on giving you an introduction to the concept of CTEs in general. It’s important to know that recursive queries are only one of the two types of CTEs. It's a good idea to take a step back and learn a little about CTEs in general, which is exactly what this article does. Remember, you can learn about CTEs without knowing about recursion, but it doesn’t work the other way round. Also, this article lists several benefits of using CTEs in general, which also applies to recursive CTEs.

Why it’s useful: It gives you a general overview of CTEs, their syntax, and their benefits. Again, it is very systematically written and uses very light and understandable language.

6. How Recursive Common Table Expressions Work

Author: Marek Pankowski

Link: https://learnsql.com/blog/recursive-common-table-expressions-work/

Description: Once more, an article you can find on LearnSQL.com. This nice article shows you the recursive CTE syntax (in SQL Server) in one example. The example is not complicated but is well designed to show you how to write a recursive query and what it does. Also, the author explains the parts of the code in a very simple way so that it doesn’t smother you with technicalities and high-tech language.

Why it’s useful: it is a focused explanation of the recursive query on a practical example. Plus, it has one additional benefit: at the end of the article, there are several hints on how to adapt recursive CTEs to PostgreSQL, MySQL, and Oracle. This is very useful to know because there are some differences in syntax between different databases.

7. Recursive SQL Queries With PostgreSQL

Author: Martin Heinz

Link: https://martinheinz.dev/blog/18

Description: This is a nice article written by Martin Heinz, and you can find it on his personal website & blog. Martin is a software developer/DevOps engineer currently working at IBM. In this article, he explains when to use recursive queries. He also gives you a code to create a table to follow his explanations of recursion. There are two real-life recursive query examples to help you practice writing recursive queries a little bit. Additionally, there’s an explanation of using recursion on graphs, infinite tables, and views, for those who want to know more.

Why it’s useful: It is a great balance of theory and practice, with very clear explanations that go into topics other articles do not mention. This article explains recursive queries on PostgreSQL, but you can adapt them even if you’re using some other database.

Learn Even More About SQL Recursive Queries!

These seven articles strike a good balance between pushing you into the unknown and not scaring you with too much information. Take your time to go through them and get a good feel for how recursive queries work and when you can use them. I’m sure you will realize how powerful they are.

You may have just started with recursions. As a next step, there’s an entire Advanced SQL track fully focused on advanced SQL topics, including window functions, GROUP BY extensions, and finally, recursive queries. You can also discover how you can practice advanced SQL with our platform.

Recursive queries are not easy, but it’s not impossible to learn. Read, learn, practice, repeat – this is recursion itself! Use it to master recursive queries in SQL.