Sunday, September 16, 2018

Arbitrary depth recursive queries in SQL

Sometimes the data in your SQL database represents hierarchical data. For example, a bill of materials, departments, or reporting structures (employee directory).

You may not know it, but you can actually do fully recursive queries directly in SQL using common table expressions (CTEs).  I wrote up a github 'gist' including example data and SQL code to demonstrate how to do this.

I'm using SQLite in this example but any SQL language that implements the WITH keyword should be able to do the same thing. If you've never used SQLite before, you are missing out on an amazing, cross-platform, open source, single-file, self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world. I encourage you to check it out.

Check out my post about recursive SQL queries here on github