Format a SQL SELECT with JOIN

Multi-table JOIN queries are the workhorse of relational database development, but they quickly become unreadable when written without consistent formatting. A query joining five tables on a single line is nearly impossible to review for correctness, optimize for performance, or debug when it returns unexpected results. The SQL formatter applies a consistent style that makes the query's intent immediately clear. This example joins users with orders and order_status tables, groups by user, aggregates order counts and total spend, filters out cancelled orders, and sorts by spend descending — a typical "top customers" reporting query. Reading the formatted version, you can immediately see what data is being selected, which tables are joined on which conditions, what the filter criteria are, and how results are grouped and ordered. Key parts of this query explained: table aliases (u for users, o for orders, os for order_status) keep the query concise without sacrificing readability. COUNT(o.id) counts the number of orders per user, while SUM(o.total) aggregates the total spend. Both aggregates are given descriptive aliases (order_count, total_spent) that appear as column headers in the result set. The GROUP BY clause lists all non-aggregated SELECT columns — this is required in standard SQL. INNER JOIN vs LEFT JOIN: this query uses INNER JOIN, which means only users with at least one non-cancelled order appear in the results. If you want to include users with no orders (showing 0 for order_count), switch to LEFT JOIN. The difference between these two is one of the most common sources of "missing rows" bugs in reporting queries. Real-world scenarios: a CRM dashboard showing top customers by revenue uses this exact pattern; a monthly report calculating user engagement metrics by cohort uses multiple aggregations in a similar structure; an admin panel searching for users who have placed orders in a specific date range filters with additional WHERE conditions. Tips and pitfalls: always check that every column in the SELECT list is either in an aggregate function or in the GROUP BY clause. Databases that follow the SQL standard strictly (PostgreSQL) will error on ungrouped columns; MySQL in some modes will silently pick an arbitrary value. The formatted query makes it easy to visually audit the GROUP BY list against the SELECT list.

SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_status os ON o.status_id = os.id
WHERE u.active = 1 AND os.name != 'cancelled'
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 20;