SQL Execution Order
Most developers writing SQL seldom think about the execution order of their SQL. Most of the time it becomes intuitive that such deep understanding may not be required. But understanding the execution order can help with writing better and performant SQL.
Below is the logical execution order followed by SQL Frames which matches with the execution order of most popular and standards compliant databases.
|selecting from the source tables
|filtering before joining
|join multiple dataset rows
|filtering pre-aggregate rows
|rollup of aggregate functions
|filtering aggregate rows
|filtering based on analytic functions
|selecting the rows
|selecting distinct rows
|set operations on rows
|ordering of the rows
|taking only a subset of selected rows
Note that this is just a logical order and not how a particular database might actually implement the physical plan. There are many factors such as indexes, types of indexes, clustering, partitioning and other concepts that influence how a query is actually executed. But in order to guarantee the same output no matter which database it is, understanding this logical order of execution is important.
QUALIFY is currently not supported by SQL Frames. However, the same functionality can
be achieved by introducing an
fdf() call to filter using the analytic fields from the parent
QUALIFY is mostly a syntactic sugar and there is no loss of functionality without the support for it.