Hey SQL users! Are you repeating the same query in every report? Are your queries getting too complicated? Organize them with recursive queries!
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 queries 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 organize your basic, much-used 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.
In this article, we’re going to assume you have enough familiarity with SQL to create reports. We might mention some rather more advanced concepts, but never fear: you can learn more about them on the Vertabelo Academy blog. If you’re a total newbie to SQL, consider the SQL Basics course, which will teach you the foundations of good SQL coding.
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
To set the stage, we will introduce the “
balance” table. If you do any kind of reporting, 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:
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 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:
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 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:
Putting It All Together
Now imagine that we want to tie it all together – use both business definitions in one SQL command. How would you do it? Advanced users might create a temporary or intermediate table, which you can learn about in this Vertabelo Academy 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.
Now reading your SQL is easier. You can simply go from top to the 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
SQL Practice Makes Perfect
You’ve learned one powerful method of decluttering your SQL code. But 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 Vertabelo Academy’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 code over and over again. It will be worth it!