Skip to main content

Sub queries

· 3 min read
Siva Dirisala
Creator of SQL Frames

There are a wide class of questions that can only be answered declaratively by using advance sql concepts like sub-queries. Sub-queries are also expensive compared to normal queries and hence one of the reasons that not all low-code platforms provide them.

"What are all the opportunities with no activity in the last two weeks" is an example of a query that requires sub-query and SQL Frames just introduced support for a wide variety of sub-queries in both SELECT and WHERE clauses.

Why Sub-queries​

When evaluating any shiny new feature, the first question should be why do I need this? Can't I use my existing tool box to solve the problem? SQL which is based on relationships can be best understood by taking a set-theoric approach. Relations, as we know can be 1-to-1, 1-to-many or many-to-many. Many low-code platforms provide the ability to navigate these relationships when the navigation from one record to another table is a 1-to-1.

As an example, an Opportunity has a Customer and the Customer record has a Location. So, it is possible to query for opportunities based out of the USA by navigating from Opportunities to Customers to Location. This is also referred to by some as dot-walking. ServiceNow platform is an example providing this dot-walking capability.

1-to-many filtering​

However, there are times when the relationship doesn't result in one record but several. In such cases, doing a straightforward join would result in the duplication of the main record several times. How do we avoid this duplication and yet have a way to filter the data based on this 1-to-many relationship? This is where subqueries in WHERE come into picture. For example, to find out all Opportunities where there is no activity (1-to-many) for at least two weeks can be done using sub-queries. In fact, it is a correlated sub-query and hence is expensive as well.

1-to-many selecting​

Sometimes the question doesn't require filtering by the 1-to-many relationship but rather requires selecting data from the child table. However, since there are many records in the child table, it most likely requires to aggregate the data from the child table and compute things like sum or avg. For example, one may wish to see all the sales orders along with their order total which is an aggregation computed from the order lines (unless of-course the total amount is explicitly denormalized on to the sales order table). This is where subqueries in SELECT come into picture.

Sub-query UX​

The UX for sub-queries can be daunting. That is why, different SAAS app platforms try to make it easy for their end users by referring them in non-technical terms. For example, Sales Force calls them as cross filters while ServiceNow calls them as related list conditions. Of course, most solutions come with limitations due to the complexity of the associated SQLs both to end users as well as to the backend servers.

When a platform doesn't offer these capabilities by default, then the developers using the platform end up writing extra ad-hoc code that is mostly slow. They do this by manually using nested loops to select/filter row-by row. This type of approach becomes very slow when there are filters and/or security conditions that need to be also evaluated in memory.

Summary​

With the introduction of subqueries in SELECT and WHERE clauses, SQL Frames is making it easy to declaratively specify the logic and not worry about manually creating such logic letting application developers focus on solving business problems.