Posted on

Query formatting – suggested good practise

What follows is just a suggestion. You may have a perfectly sensible different way of accomplishing the same thing, and that’s fine. Actually, feel free to post your own methods in a comment. Anyway, here we go…

/*appname:module:class:method:symbolicname*/
SELECT tn.olname, AGGREGATE(ot.col) AS aliascolname, ...
   FROM tblname tn
   JOIN othertable ot ON (ot.id=ft.id)
   LEFT JOIN footable ft ON (ft.id=tn.id)
  WHERE tn.somecol = 123
    AND (tn.whatever > NOW() OR ...)
  GROUP BY tn.colname
  HAVING ...
  ORDER BY ...
  LIMIT ...

Putting the comment into the query right at the start allows you to easily find it in SHOW PROCESSLIST or a log file (such as slow query log) and spot where it comes from, even if the end of the query somehow gets truncated. It’s a fabulous tool for keeping track of queries and keeping apps maintainable. If you are a DBA, you will love instilling this behaviour with the developers….

Use explicit join syntax rather than comma; no different for the server, but it’s more readable. I put the join condition (ON or USING syntax) next to the join it belongs to, so it doesn’t get “lost” in the WHERE clause of a big query. You could say INNER JOIN rather than just JOIN, but that’s a detail.
If you actually *need* a cartesian product (no join condition), say CROSS JOIN to make it clear that you didn’t just forget the ON clause…. so people won’t wonder about it later.

Be explicit about which table a column belongs to, with big queries it just becomes difficult to read otherwise. By using shorthand aliases for table names, this is not a big deal. And it’s all the same for the server, this is just for you and others’ benefit.

Use caps for keywords, and use lowercase, underscore_lower_case, or CamelCaps for table/colnames.
Split the query into multiple lines as seems appropriate for readability. You can even insert extra comments with /* … */ at any point.

In expressions, be liberal with use of (). Implicit evaluation order is cool but explicit is more readable. It allows your brain to understand what’s going on without having to make the “what happens first” assessment.

These few things will sooo help you, your colleagues, anyone else ever dealing with your queries or installation later, and if you ask a question somewhere it’s much easier for someone unfamiliar with your app to actually read your magic and help you.
Enjoy!

(just in case – there may well be syntax or other errors in the sample query above; feel free to tell me and I’ll fix it up, just to make sure noone is distracted by such probs – thanks)

Posted on