These are my personal rants and opinions and they do not necessarily reflect the opinions of my family, employer, religion, volunteer organizations, social group, or any other group, organization, or person with whom I may be affiliated.

Sunday, September 16, 2018

Arbitrary depth recursive queries in SQL

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.