Simplify SQL Code: Recursive Queries in DBMS

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!