The Shift in Priorities
In the past, we wrote cryptic SQL to save CPU cycles. We avoided joins, used short aliases, and packed logic into stored procedures. Today, cloud databases are powerful and scalable. The bottleneck is no longer the CPU; it's the developer's cognitive load. Develop Box Privacy-first DevTools help reduce this load by providing clear formatting and visualization.
If your team cannot understand a query, they cannot maintain it. A "fast" query that is buggy is worse than a "slow" query that is correct.
Old School vs. Modern SQL
| Practice | Old School (Avoid) | Modern Standard (Adopt) |
|---|---|---|
| Structuring | Deeply nested Subqueries | Common Table Expressions (CTEs) |
| Joins | Implicit (Comma) Joins | Explicit JOIN ... ON |
| Aliasing | t1, t2, a, b |
users, orders, u, o |
| Formatting | All one line | Indented, Keywords Uppercase |
Why CTEs Win
Common Table Expressions (WITH clauses) read top-to-bottom, like code. Nested subqueries read inside-out. By using CTEs, you tell a story: "First, get the active users. Then, calculate their revenue. Finally, filter the top 10." This linear flow matches how human brains process logic.
JSON in SQL
Modern databases (Postgres, MySQL 8) have excellent JSON support. You don't need a separate MongoDB instance just to store some flexible metadata. Use a JSONB column for user settings or feature flags, and keep the core relational data in structured columns.
Index Strategy: Less is More
Don't index every column. Indexes slow down INSERT and UPDATE operations. Only index columns that are frequently used in WHERE, JOIN, or ORDER BY clauses. Use EXPLAIN ANALYZE to verify if your index is actually being used.
Frequently Asked Questions
Should I use SELECT *?
Avoid it in production. It fetches unnecessary data, increasing network load, and can break code if columns are added/removed. List columns explicitly.
Why are implicit joins (comma joins) bad?
They mix filtering logic (WHERE) with join logic, making the query harder to read and easier to break. Explicit JOIN ... ON separates these concerns.
When should I use a JSON column in SQL?
Use JSON columns for flexible, sparse data (like user preferences or feature flags) that doesn't fit well into a rigid schema.