General Guidelines

• 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

Simple Queries

For simple queries, put each clause on its own line:

SELECT products.id, SUM(inventory.price)
FROM products
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
LIMIT 50;

For very short queries, a one-liner will do:

SELECT products.* FROM products ORDER BY products.name ASC

Complex Queries

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.

Subqueries

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;

UNIONS

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

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
FROM consumer_customers
ORDER BY RAND()
LIMIT 500