The Art of Writing Clean, Readable SQL

SQL is one of those languages where the gap between "works" and "well-written" is enormous. A query that returns correct results can still be nearly incomprehensible. I've debugged queries at 2 AM that made me question my career choices. Don't write queries that will make future-you curse current-you.

Naming Conventions Matter

Use descriptive names. user_id, created_at, total_amount—these tell you what they contain without needing to think. a1, temp, data are not names; they're placeholders for confusion.

Pick a convention and stick to it. snake_case for columns (user_id), PascalCase for tables (UserAccounts), all lowercase for keywords (SELECT, FROM).

Format for Scanning

Your eyes scan code, not read it character by character. Structure your queries to facilitate scanning: keywords on separate lines, conditions indented, related items visually grouped.

SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.total) as lifetime_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
    AND u.created_at >= '2023-01-01'
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 5
ORDER BY lifetime_value DESC

Avoid SELECT *

SELECT * is a database antipattern in production code. You get columns you don't need, you might break code if the schema changes, and you can't tell what's being used without reading the consuming code.

List the columns you need. Yes, it's more typing. No, that's not a good reason to write worse code.

Use CTEs Instead of Nested Subqueries

Common Table Expressions (CTEs) make complex queries dramatically more readable. Instead of nesting subqueries, build them step by step.