Recursive CTEs, explained from first principles

Cynthia Sophie Mwangi — Fri, 04 Sep 2026 13:17:17 +0000

Some data isn't flat. An employee has a manager, who has a manager, who has a manager, all the way up to the boss at the top. A comment can be a reply to a reply to a reply. A folder can sit inside a folder inside a folder. This kind of data, where things are connected in layers or "levels," is called hierarchical data. Think of a family tree, or the folders on your computer. Each item points up (or down) to another item of the same kind.

The tricky part: you never know ahead of time how many layers deep the chain goes. One employee might be 2 levels from the top, another might be 6. A recursive CTE is the tool SQL gives you to handle exactly this. A query that keeps digging one layer deeper, automatically, until it runs out of layers.

First, what's a CTE?

CTE stands for Common Table Expression.

Don't let the name intimidate you, all it means is: "give this block of query a name, so I can use it again further down, later in my script." You write it with the keyword WITH.

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2026-01-01'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

Here, recent_orders isn't a real table sitting in your database. It's a temporary result, given a name, that only exists for this one query.

why not just write a normal query?
A CTE is really just a nicer way of writing something you could squeeze into one messy query anyway; it just keeps things readable by breaking the logic into named, ordered steps instead of one big nested blob.

A recursive CTE takes this one step further: the named result (from the CTE) is allowed to refer to itself. That's the entire trick behind it and it's the only way a query can "loop" over hierarchical data of unknown depth.

Why a normal query can't do this

Picture a simple company organization chart: the CEO, and everyone below them, layer by layer.

This is a hierarchy: everyone connects upward to one person, layer by layer, and you don't know how deep it goes until you get there.

A "normal" query can only join a table to itself a fixed number of times. So if you wanted everyone under the CEO with plain joins, you'd have to write something like: join employees to their manager, then join that result to the next manager up, then join that to the next one and stop wherever you guessed the chart ends. If someone gets hired 7 levels deep next year, your query silently misses them.

A recursive CTE removes the guesswork. You tell it where to start and how to take one step outward and it keeps repeating that one step, on its own, until there's nobody left to find.

The two parts of a recursive CTE

Every recursive CTE is built from two pieces, glued together with UNION ALL (which just means "stack these two result sets on top of each other"):

The anchor -> this runs once, and only once. It's your starting point: "give me the person with no manager" (the CEO), or "give me the top-level category."
The recursive part -> this is the piece that repeats. Each time it runs, it looks for "who connects to the people I just found," and it keeps doing that, again and again, until a round comes back completely empty.

Here's the org chart example written out as SQL. It maps directly onto the two pieces above:

WITH RECURSIVE employee_chain AS (

  -- THE ANCHOR: find the one person with no manager (the CEO)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- THE RECURSIVE PART: find everyone whose manager we just found
  SELECT e.id, e.name, e.manager_id, ec.level + 1
  FROM employees e
  JOIN employee_chain ec ON e.manager_id = ec.id

)
SELECT * FROM employee_chain;

a note on syntax
WITH RECURSIVE is what Postgres and SQLite expect. MySQL (version 8 and up) and SQL Server are happy with just WITH — no extra word needed. Small detail, but it trips people up.

You don't need to think of this as clever. It's just "keep repeating one step", automated.

Watching it run, step by step

The easiest way to actually understand this is to stop thinking of it as one magic statement, and picture the database running it in rounds:

The database is just doing the "join, join, join" you'd otherwise write by hand, and it's smart enough to know when to stop.

The one thing to be careful of:
If your data has a loop in it by mistake say, an employee who is (accidentally) listed as their own manager's manager, the recursive part will never come back empty, and the query will run forever. Most databases let you set a safety limit: SQL Server has OPTION (MAXRECURSION n), and elsewhere you can add your own cutoff, like WHERE ec.level < 20, inside the recursive part.

Is there such a thing as a "recursive subquery"?

You'll hear this phrase sometimes, but strictly speaking, no, and understanding why makes the whole topic click better.

A subquery is a query with no name, tucked inside another query.

SELECT name
FROM employees
WHERE manager_id = (
    SELECT id FROM employees WHERE name = 'Jane'
);

Recursion needs something to call back to by name - and an unnamed query has nothing to point at. A CTE gets a name the second you write it, and that name is exactly what the recursive part calls. No name, no recursion. That's really the entire difference.

What people usually actually mean

Oracle's older way: CONNECT BY

Before recursive CTEs existed in the SQL standard, Oracle already had its own way of walking a hierarchy: CONNECT BY, paired with a built-in "column" called LEVEL. It solves the exact same problem, it's just written as a subquery tacked onto a normal SELECT, instead of a self-referencing WITH block.

SELECT employee_id, name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

CONNECT BY looks like one plain SELECT statement, which is probably why people call it a "recursive subquery" but it's doing the same job as a recursive CTE.

One nice thing Oracle gives you for free here: LEVEL automatically tells you how deep you are in the hierarchy. In a recursive CTE, you have to build that counter yourself, like we did with level + 1 earlier.

CONNECT BY only works in Oracle. Modern Oracle also supports the standard WITH RECURSIVE CTE and most new projects use that instead, since it's the version that works, unchanged, on Postgres, SQL Server, MySQL 8+, and SQLite too.

The one idea to hold onto

A recursive CTE isn't really a special trick. It's a pattern with a name.

Pick a starting point. Define one step that reaches one layer further out. Let the database repeat that step on its own, until nothing new is left to find.
Once that clicks, every hierarchy: org charts, comment threads, category trees, folders inside folders, starts looking like the same problem, just wearing a different table name.

Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Cynthia Sophie Mwangi — Sat, 09 May 2026 08:01:22 +0000

Introduction

Data Modeling, the beginning of making sense out of data. This is one of the most important steps to dealing with your data. This means that your most complex and detailed dashboards will not be appreciated without clean and well done data modeling.

In this guide we will go through

Data Modeling
All SQL joins
Power BI relationships and how they differ from joins
Fact and Dimension tables
Data Modeling Schemas

What is Data Modeling

Data modeling refers to the process of structuring and organizing data so that it can be analyzed effectively. Think of this as cleaning a messy room and putting everything where it belongs: all books in the shelves, clothes on your hangers and shoes in the shoe-rack.

In Power BI this means: defining tour tables, creating relationships and optimizing performance and accuracy.

Think of it like designing a map: if roads (relationships) are wrong, you’ll never reach the right destination (insights).

SQL Joins

When working with data, it’s rare to find everything you need in one table. In real-world databases, data is split into multiple tables to keep things organized and efficient.

That’s where SQL joins come in.

A SQL join is used to combine data from two or more tables based on a related column (usually a key).

Imagine analyzing a business given these tables:

A Products table that tells you what is sold
A Customers table that tells you who is buying
A Sales table that records transactions

But the Sales table only contains IDs(product id and customer id), not full details (like customer name and product name/product price).

So if you want to answer: “Who bought what?” You must join the tables.

Types of SQL Joins

Let’s break them down. Shall we?

1. INNER JOIN (Only Matching Data)

This join is used when you want to find only records that exist in both tables, in other terms matching rows

LEFT JOIN (Keep Everything from the Left Table)

Also known as the left outer join.
This join is used when you want to return only records in your left table that also exist in your right table. “Show me everything on the left, even if there’s no match in the right.”

RIGHT JOIN (Keep Everything from the Right Table)

Also known as the right outer join.
This join is the polar opposite of the left join. It is used when you want to return everything in the right table that also exist in the left table.

Joins are relating different tables using a related column.
Say you have a table on sales - that has different products sold. One on products - with a list of the products available for sale and another on customers - detailing the unique attributes of your customers.

To relate the tables (get more information not available in our main table -sales) we use the primary key marked(PK) from our dimension tables (customers and products) and find the matching input in the foreign key columns in the fact table (sales)

The process of ralting these table requi

DEV Community: Cynthia Sophie Mwangi