SQL Development Tip: Write in Execution Order

Ever struggle when writing a database query?

Then let me share a great tip that’s proven to be extremely useful when helping someone learn how to write SQL queries. And it’s not just for beginners…I’ve even had people who’ve been writing SQL a long time find great benefit from this one tip.

Write in Execution Order

SQL is logically executed in a different order than it is written. If you think of the 6 main clauses of a statement, they are in written order ->> select, from, where, group by, having, and order by. While You can think of it being executed as ->> from, where, group by, having, select, order by. I’ve found that it’s so much easier to write and think through if you write it in execution order instead of written order

I always start my queries with   select *   just to make sure it’s syntactically correct and come back to it later to put in the columns I need. Making the query syntactically correct from the beginning helps your development tool of choice give you all those nice prompts and auto completions. Then I write my from clause and think through all the joins, if any. This is where most of the benefit is because once this is written, it's the foundation for the rest of the logic in the query.

Next I filter, group and filter again with having as necessary for the query. Once that’s all done, I go back and fill in all the columns I need in the select clause. This is now much easier because I have my aliases set and all the available columns are there to pick from. If needed, then write the order by clause. Writing the query in this order helps you to think through the query step by step rather than all at once or out of sequence.

If you’re struggling with writing queries, I highly recommend writing your queries in execution order. It makes the process so much more logical and easier to think through. 

I have seen many people struggle to write a query, agonizing over the select clause, trying to predict alias names, writing functions, worrying about filters and table grains, and all sorts of confusing problems. 

Next time, write the from clause first and see how much easier it is to build a query.