Posts

Showing posts from May, 2015

The power of SQL recursive queries

Image
Get to know the power of SQL recursive queries Level: Advanced Most commonly, the SQL queries we run on a database are quite simple. Well, that depends on your role, of course. Analysts in data warehouses retrieve completely different sorts of information using (very often) much more complicated queries than software engineers creating CRUD applications. However, sometimes it's simpler or more elegant to run a query that is a little bit more sophisticated without needing further data processing in the code. One way to accomplish this is with a SQL feature calledrecursive queries. Let's take a real-life example. Let's assume we've got a database with a list of nodes and a list of links between them (you can think of them as cities and roads). Our task is to find the shortest path from node 1 to node 6. Well, in fact, it's nothing more than graph traversal. The very first idea an average software engineer may have would be to get all rows from both tables and implement a …