I like to follow quite strict rules when it comes to coding MySQL queries. Unfortunately, a lot of developers seem to disregard the need for readable and maintainable SQL code which becomes a problem when working with joins across multiple tables and nasties like sub querying.
There are some rules I stick to:
- MySQL clauses, functions, statements etc. are typed in upper-case. Because SQL doesn’t make much use of control characters such as brackets to define code blocks, it inhibits readability if you can’t easily identify where the clauses are at a glance.
- I nearly always specify a column set when I’m running a SELECT query, as opposed to always selecting everything. There is a performance boost when working with database tables which have many columns and large amounts of data. I ran a test on a table with 23 columns and ~160,000 rows of data. The average time for a query on all columns took 1.5818 seconds to complete. When querying on 10 columns of data, the average query time was reduced to 0.259 seconds.
- I usually prefix all columns in the SELECT clause of my queries with the database which I am referencing. It adds quite a lot of extra key-strokes but it makes searching large projects for instances of column usage a hell of a lot easier! It also means that if I ever need to alter a query to join onto another table with ambiguous column names, I usually don’t end up having to re-write large portions of my code.
- I enclose all of my table and column names in back-ticks. Again, this convention aids searching for snippets of SQL but it also means that I don’t run into any problems with column names containing any unusual (yet legal) characters or reserved words.
- Any conditionals which apply to joined tables are placed within the JOIN clause(s) of the query. I find it easier to keep everything in once place – it looks neater, too.
- This one’s personal preference, but I like to split my clauses and expressions up with new lines and I indent the expressions in from their containing clauses. Proper indentation is the cornerstone of readable code and I don’t see why SQL has to be any different.
As an example of a typical SQL query I may use:
SELECT `customers`.`name_first`, `customers`.`name_last`, `addresses`.`line_1`, `addresses`.`town` FROM `customers` INNER JOIN `addresses` ON `addresses`.`customer_id` = `customers`.`id` AND ( `addresses`.`default` = 1 OR `addresses`.`approved` = 1 ) WHERE `customers`.`approved` = 1 ORDER BY `customers`.`company_name` ASC, `customers`.`name_last` ASC, `customers`.`name_first` ASC, `addresses`.`date_added` DESC;
I know that these conventions aren’t everyone’s cup of tea. Discuss your own preferences in the comments, below.