# Recursive Selects using Common Table Expressions

| by Jonathan Allen 323 Followers on Oct 05, 2007. Estimated reading time: 1 minute |

A note to our readers: As per your request we have developed a set of features that allow you to reduce the noise, while not losing sight of anything that is important. Get email and web notifications by choosing the topics you are interested in.

Relational databases are great for storing most forms of structured data. The most notable exception is recursive data. Tree-like structures, essential for menus, normally require awkward stored procedures to efficiently return. SQL Server 2005 does have an answer though.

Common Table Expressions, or CTEs, were introduced in SQL Server 2005, but they have not been getting the attention they deserve. In their simplest form, they are a named sub-query that appear before the main query. Since the CTE can be used multiple times, it can make complex queries look significantly cleaner.

The syntax is easy to use, but does have a few points to remember.

;WITH FirstCTE (column1, column2) AS
(SELECT column1, column2 FROM MyTable),

SecondCTE(column1, column2) AS
(SELECT column1, column2 FROM OtherTable)

Select * FROM FirstCTE UNION ALL Select * FROM SecondCTE

Multiple CTEs can be chained together, but they must be used immediately after being defined; no other code can appear between the last CTE and the final query. The semi-colon is only required if the CTE isn't the first statement in the batch, but it is easier to use it every time. CTEs cannot use ORDER BYor COMPUTE, as they are essentially sub-queries.

CTEs becomes really interesting when used recursively. An article in October's MSDN Magazine outlines the rules of recursion.

1. Create the query that returns the top level (this is the anchor member).
2. Write a recursive query (this is the recursive member).
3. UNION the first query with the recursive query.
4. Make sure you have a case where no rows will be returned (this is your termination check).

For example,

(
-- Anchor Query
UNION ALL
-- Recursive Query
)

Style

## Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

## Get the most out of the InfoQ experience.

### Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Other vendors?

Nice post either way, but something I've never run across is even a shallow overview of the recursive solutions for several of the most popular databases.

As far as I know, Oracle has this with the SQL99 "WITH" clause. I think PostgreSQL may have a module to support the same syntax. I'm not aware of any recursive query support for MySQL.

Alternative

A vendor-neutral alternative tactic I've seen is to use an ancestry path column, ie. a column on the row containing a path of ancestor IDs, eg "/1/2/3/4". It is then possible to do some fairly sophisticated querying/updating on this using just LIKE and wildcards.
Kit

Re: Other vendors?

Oracle has supported recursive SQLs for years with the "CONNECT BY" syntax:

select    node_id,    parent_node_id,    level -- pseudo-columnfrom    nodeconnect by    prior node_id = parent_node_idstart with    node_id = 'TOP';

Rescursive triggers on a single table

I know this is a off topic but as far as SqlServer 2005 goes, I was a little disappointed when I discovered it still wasn't able to do triggers on self referencing tables. So, just a little bit of public exposure might make people at Redmond work a bit harder :)
forums.microsoft.com/TechNet/ShowPost.aspx?Post...
connect.microsoft.com/SQLServer/feedback/ViewFe...

Re: Other vendors?

To be honest, I simply don't have the time to keep up with all the database vendors. But if you feel your favorite databases are being slighted, let me know and I'll try to run more articles on them.

As for Oracle and CTEs, here is a link for our other readers.

www.dba-oracle.com/t_sql99_with_clause.htm

If anyone has something for other vendors, please post it here.

Re: Rescursive triggers on a single table

Well if you want to write a brief article on why it is needed, I'll be happy to run it as a "guest opinion". I can't promise it will help, but maybe we can garner a few more votes on Microsoft Connect.

Re: Alternative

Or the nested set model. Great for read heavy operations.

www.developersdex.com/gurus/articles/112.asp

Re: Other vendors?

i'm kinda outdated, and disapointed about the "WITH" syntax is not applicable in mysql.

anyone could give me a clue that the following codes in sql2005 translated in mysql?

thanks for respond.
******************************************************
(
-- Anchor Query
UNION ALL
-- Recursive Query
)
******************************************************
Close

#### by

on

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

8 Discuss

Login to InfoQ to interact with what matters most to you.