Back to articles list December 12, 2017 - 6 minutes read Simplify SQL Code: Recursive Queries in DBMS Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: common table expressions CTE data modifying how to in sql recursive queries SQL for advanced WITH Hey SQL users! Are you repeating the same query in every report? Are your queries getting too complicated? Use recursive queries to simplify SQL code! Too many SQL reports can lead to clutter on your desktop and in your head. And is it really necessary to code each of them separately? Ad-hoc queries can share much of the same SQL code with managerial reports and even regulatory reports. Suppose you’ve been writing basic SQL code for a while. Eventually, you realize something: your reports have repeating parts. Just like in Excel, where one spreadsheet becomes the basis for a whole plethora of reports, you’re repeatedly retyping the same SQL snippet. Surely there is a better way. Don’t worry; this is a common problem. You can simplify SQL code blocks in such a way that you don’t have to keep retyping them; you can just reuse them as needed. This is called using recursive queries, or Common Table Expressions (CTEs), and it will save you time and reduce the likelihood of embarrassing mistakes. likelihood of embarrassing mistakes. In this article, we’re going to assume you have enough familiarity with SQL code to create reports. We might mention some rather more advanced concepts, but never fear: you can learn more about them on the LearnSQL.com blog. If you’re a total newbie to SQL, consider the SQL Basics course, which will teach you the foundations of writing good SQL code. Simplify SQL Query: Setting the Stage CTEs may seem like a more complex function than you’re used to using. However, they have another (and less intimidating) name: the WITH function. For a comprehensive overview of using CTEs, you can check out this course. For now, we’ll just show you how to get your feet wet using WITH and simplify SQL queries in a very easy way. To set the stage, we will introduce the “balance” table. If you do any kind of reporting using SQL code, you have surely worked with some form of this table. This table contains the “balance” amounts of certain “accounts” on certain “dates” and times. To make things easier, we will look at the balance at the end of the day. For savings accounts, the balance will contain a positive amount; for debit accounts, the balance will be negative, since it is an amount owed. However, we will not necessarily show the balance of debt accounts as a negative value. Since we are dealing with reporting, we will also need to be able to extract accounts by their “account_id”. And because one client can have multiple accounts, we also need a “client_id” in this table. Check out how this table looks in Vertabelo, a data modeling tool that lets you design databases visually: Simplify SQL Query: Extracting Debt Amounts As a business analyst, it’s your job to know about the debt amounts for various accounts. That knowledge represents a business definition. In this case, any account that starts with 6 is a debit account. Suppose we are extracting the debit account information for client #101. We can do this with a simple SQL aggregation query: SELECT sum(value) AS debt_value FROM balance WHERE id_account LIKE '6%'and id_client = '101' Hint: If you’re new to aggregation, this article is a good place to learn more about it. The result is the debit for all accounts with the client ID of 101: debt_value 940 Simplify SQL Query: Extracting Credit Amounts Now suppose we want to see the credit amount for client #101. This is another business definition. In this case, any account that starts with 2 is a credit (or savings) account. As you can see, we will use a very similar SQL query to get this information: SELECT sum(value) AS credit_value FROM balance WHERE id_account LIKE '2%'and id_client = '101' Here is the result: credit_value 14790 Putting It All Together Now imagine that we want to tie it all together – use both business definitions in one command using SQL code. How would you do it? Advanced users might create a temporary or intermediate table, which you can learn about in this LearnSQL.com course. Other users might repeat the code snippet as a subquery and use it as many times as needed. But one of the easiest ways to structure your code is by using one or more recursive queries, or WITH functions. These enable you to name and query an SQL snippet, so you can use it as you would any other function. Let’s name our snippet “debt” and query it like a table: WITH debt AS ( SELECT sum(value) AS debt_value FROM balance WHERE id_account LIKE '6%'and id_client = '101' ) SELECT * FROM debt; Another bonus to using recursive queries is that you can name and use as many snippets as needed in your SQL code. Let’s say we are constructing a report about the debt and credit value for every customer. We want to organize this information in the same line, i.e. one line per customer. How would we do this? WITH debt AS ( SELECT id_client, sum(value) AS debt_value FROM balance WHERE id_account LIKE '6%' GROUP BY id_client ), credit AS ( SELECT id_client, sum(value) AS credit_value FROM balance WHERE id_account LIKE '2%' GROUP BY id_client ) SELECT debt.id_client, debt_value, credit_value FROM debt JOIN credit ON (debt.id_client = credit.id_client); The result is: id_client debt_value credit_value 104 100 100 103 100 200 106 100 100 101 940 14790 Notice that I added a GROUP BY clause to both SQL snippets. You can learn more about GROUP BY here. I needed to do this so we could perform a meaningful JOIN on the different code snippets. As you can see, we used the WITH keyword only once, even though we set up two recursive queries (debt and credit). You’ll see the name for each CTE immediately before the AS keyword. This is how you simplify SQL code. You can simply go from top to bottom, like you would with any other chunk of text. You can also implement other business requirements using this base, like calculating a general balance for each client: WITH debt AS ( SELECT id_client, sum(value) AS debt_value FROM balance WHERE id_account LIKE '6%' GROUP BY id_client ), credit AS ( SELECT id_client, sum(value) AS credit_value FROM balance WHERE id_account LIKE '2%' GROUP BY id_client ) SELECT debt.id_client, debt_value - credit_value balance FROM debt JOIN credit ON (debt.id_client = credit.id_client); Some accounts will have a negative balance, since these clients owe more money than they have in their credit (savings) account: id_client balance 104 0 103 -100 106 0 101 -13850 Already Know How to Simplify SQL Query? You’ve learned one powerful method of decluttering your SQL code. You can use it to simplify SQL queries, but remember that this method, like any other, can only really be perfected with hands-on experience. You can get this experience in your daily work (a bit risky) or by taking one of LearnSQL.com’s interactive courses. For now, revisit some of your previous queries and start rewriting them using recursive queries. This will make them more readable and relieve you from rewriting the same blocks of SQL code over and over again. It will be worth it! Tags: common table expressions CTE data modifying how to in sql recursive queries SQL for advanced WITH You may also like Do it in SQL: Recursive SQL Tree Traversal Ever heard of the SQL tree structure? In this article, you’ll learn to use the recursive SQL tree traversal on the example of a website menu. Read more SQL Statistical Analysis Part 1: Calculating Frequencies and Histograms If you wonder whether you can perform statistical analysis in SQL, the answer is ‘yes’. Read my article to learn how to do this! Read more SQL Statistical Analysis Part 3: Measuring Spread of Distribution In this article, we’ll explain how to find the spread of a distribution in SQL to take your SQL statistical analysis skills to the next level! Read more High Performance Statistical Queries –Skewness and Kurtosis In this article, I am explaining the third and fourth population moments, the skewness and the kurtosis, and how to calculate them. Read more Introduction to Reporting with SQL — the Ultimate Tutorial for Business Professionals The right business decision-making is not based on intuition but data analysis. Learn how to perform simple SQL queries for data reporting. Read more Digging Into Data: Explore and Analyze Survey Results With SQL You've carried out a customer survey and collected all the results. What's next? It's time to dig into your data and here comes SQL to help you out! Read more 18 Useful Important SQL Functions to Learn ASAP Almost every company needs someone with knowledge of SQL. Structured Query Language is commonly used for business intelligence (BI), analysis, computation. Read more How to Draw a Christmas Tree in SQL Learn how to use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. Read more SQL Statistical Analysis Part 2: Calculating Centers of Distribution In this article, we’ll focus on calculating centers of distribution. We’ll learn how to calculate the SQL median, SQL mode, and various types of mean. Read more How to Use LIKE in SQL: SQL Pattern Matching SQL pattern matching is a very important and useful ability. In this article, we look at how you can match patterns using LIKE in SQL. Read more SQL Development? Structured Query Language and Apps What’s SQL development? We look at the app development process, how it intersects with modeling a database, and what SQL developers can do outside of their app. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.