- Use table aliases sparingly and only when absolutely necessary (a few extra keystrokes now will save you hours of gazing at cryptic queries later)
- CAPITALIZE keywords religiously
- Whitespace and tabs are your friends
For simple queries, put each clause on its own line:
SELECT products.id, SUM(inventory.price)
JOIN inventory ON products.id = inventory.id
WHERE products.name = 'something'
GROUP BY products.id
HAVING SUM(inventory.price) > 0
ORDER BY products.name ASC
For very short queries, a one-liner will do:
SELECT products.* FROM products ORDER BY products.name ASC
Queries with more than two or three clauses or with long lists of columns or expressions warrant a more developed syntax structure:
SELECT products.id, products.name, AVG(inventory.price), SUM(inventory.stock) AS stock_level, MAX(shipments.eta) FROM products JOIN inventory ON products.id = inventory.id JOIN shipments ON inventory.shipment = shipments.id WHERE products.name = 'something' AND shipments.delivered = 0 GROUP BY products.id ORDER BY products.name ASC LIMIT 50;
Note the whitespace and indentation. This format is incredibly effective for large or complex queries, especially INSERTs, UPDATES, and REPLACEs.
Use indentation to set off subqueries:
SELECT products.id, products.name FROM products WHERE products.id IN( SELECT DISTINCT product FROM orders WHERE status = 'pending' ) ORDER BY products.name ASC LIMIT 50;
Clearly separate queries in a UNION:
SELECT id, name FROM business_customers WHERE time_stamp > '2009-05-21' UNION SELECT id, CONCAT('fname', ' ', 'lname') AS name FROM consumer_customers WHERE time_stamp > '2009-05-21'
Advanced queries can be constructed like so. Use indentation and whitespace where it makes sense, and in simpler queries put each clause on its own line:
CREATE TEMPORARY TABLE 'tmp_235kd235sl' AS
SELECT id, CONCAT('fname', ' ', 'lname') AS name
ORDER BY RAND()