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.