SQL Best Practices: Hard-Learned Lessons from my years as a Software Engineer

A database is like a joke — if you have to explain it, it’s probably not designed well…

Picture this: It’s 3AM on a Saturday morning, and I’m sitting at my desk, three hours into what should have been a routine production data batch job. The task seemed straightforward — process a dataset of 1,200 customers. What I didn’t anticipate was that each API call would trigger thousands of database operations behind the scenes, turning a simple job into a performance nightmare.

That sleepless Saturday night taught me more about SQL optimization than any computer science course ever had. While the business team eventually got their data (with some very patient waiting), I walked away with something far more valuable: a deep respect for both the power and the potential pitfalls of SQL queries.

Whether you’re a junior developer just starting out or a seasoned architect, I hope sharing these hard-earned lessons helps you avoid the same painful experience I went through that night.The Foundation: Writing Readable SQL

Before we dive into performance optimizations and advanced techniques, let’s talk about something that saved my sanity countless times: writing SQL that humans can actually read.

I used to write queries like this:

select o.id,o.total,c.name,c.email from orders o join customers c on o.customer_id=c.id where o.created_at>='2023-01-01' and o.status='completed' order by o.created_at desc;

It works, but good luck debugging it at 3AM when there is a production issue. Now I write the same query like this:

SELECT 
    o.id,
    o.total,
    c.name,
    c.email
FROM orders o
JOIN customers c 
ON o.customer_id = c.id
WHERE o.created_at >= '2023-01-01'
    AND o.status = 'completed'
ORDER BY o.created_at DESC;

The difference isn’t just aesthetics—it’s about maintainability. When you’re debugging a complex query at 3AM, you’ll thank yourself for the extra whitespace and clear formatting.

Here’s my formatting philosophy:

• Keywords in UPPERCASE: Makes the structure obvious at a glance
• One condition per line: Easy to comment out during debugging
• Consistent indentation: Shows the logical structure
• Meaningful table aliases: u for users, o for orders, not a, b, c

The Performance Disaster That Could Change Everything

Take this SQL query, for instance:

SELECT 
    o.id,
    o.total,
    c.name,
    p.name as product_name
FROM orders o
JOIN customers c 
  ON o.customer_id = c.id
JOIN order_items oi 
  ON o.id = oi.order_id
JOIN products p 
  ON oi.product_id = p.id
WHERE p.category = 'electronics'
    AND o.created_at > '2023-01-01';

Looks like a normal, reasonable query, right? The problem is subtle but devastating: while the JOINs appear correct, there’s a logical flaw in what the query is actually requesting. Without proper constraints, what should return customer purchase patterns instead creates a Cartesian product — every customer matched with every product in the catalog.

With 50,000 customers and 10,000 products, this query would attempt to create 500 million rows of meaningless data. The database server would grind away, trying to fulfill this seemingly innocent request for half a billion rows of “customers who might theoretically be interested in products they’ve never seen.”

The fix is straightforward — restructure the query to ensure we only examine actual purchases:

SELECT 
    c.id,
    c.name,
    p.name as product_name,
    COUNT(*) as purchase_count
FROM customers c
JOIN orders o 
  ON c.id = o.customer_id  -- The crucial connection!
JOIN order_items oi 
  ON o.id = oi.order_id
JOIN products p 
  ON oi.product_id = p.id
WHERE p.category = 'electronics'
    AND o.created_at > '2023-01-01'
GROUP BY c.id, c.name, p.name
ORDER BY purchase_count DESC;

The lesson is profound: always double-check your JOIN conditions and think through what your query is actually asking for. It’s not enough for a query to run without errors — you need to ensure it’s logically sound and performance-conscious.

Understanding Indexes: Your Query’s Best Friend

Indexes are like the table of contents in a book. Without them, the database has to read every single row to find what you’re looking for. With them, it can jump directly to the relevant data.

Here’s what I wish someone had told me early in my career:

1. Index Your WHERE Clauses

-- If you query like this often
SELECT * FROM users WHERE email = 'john@example.com';

-- You need this index
CREATE INDEX idx_users_email 
 ON users(email);

2. Composite Indexes for Multiple Conditions

-- For queries like this
SELECT * FROM orders 
WHERE customer_id = 123 
    AND status = 'pending' 
    AND created_at > '2023-01-01';

-- This composite index is much more effective than three separate indexes
CREATE INDEX idx_orders_customer_status_created 
ON orders(customer_id, status, created_at);

The order matters in composite indexes. Put the most selective column first (the one that filters out the most rows).

3. Don’t Over-Index

Every index speeds up SELECT queries but slows down INSERT, UPDATE, and DELETE operations. I once worked on a system where someone had created 50 indexes on a single table. Updates were taking 30 seconds because the database had to maintain all those indexes.

My rule now: only create indexes that you actually use. Monitor your query patterns and add indexes based on real usage, not hypothetical scenarios.

The Art of Writing Efficient Queries

Use EXISTS Instead of IN for Subqueries

I used to write subqueries like this:

-- Slower for large datasets
SELECT * FROM customers 
WHERE id IN (
    SELECT customer_id FROM orders 
    WHERE created_at > '2023-01-01'
);

But EXISTS is usually faster:

-- Faster and more readable
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
        AND o.created_at > '2023-01-01'
);

Why? EXISTS can stop as soon as it finds one matching row, while IN has to evaluate the entire subquery.

Avoid SELECT * Like the Plague

This seems obvious, but I still see it everywhere:

-- Don't do this
SELECT * FROM orders WHERE customer_id = 123;

-- Do this instead
SELECT id, total, created_at FROM orders WHERE customer_id = 123;

SELECT * is problematic because:

• It transfers unnecessary data over the network
• It breaks when table structure changes
• It prevents some query optimizations
• It makes your intent unclear

Use LIMIT Wisely

Always use LIMIT when you don’t need all rows:

-- If you only need recent orders
SELECT id, total, created_at 
FROM orders 
WHERE customer_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

This is especially important for debugging. Instead of running a query that might return millions of rows, add LIMIT 100 to see a sample of the data first.

JOIN Strategies That Actually Work

JOINs are where queries get complex, and where I’ve made most of my mistakes. Here’s what I’ve learned:

Inner JOIN vs LEFT JOIN vs RIGHT JOIN

Understanding the difference saved me countless hours of debugging:

-- INNER JOIN: Only returns rows that exist in both tables
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o 
ON c.id = o.customer_id;

-- LEFT JOIN: Returns all customers, even those without orders
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o 
ON c.id = o.customer_id;

-- RIGHT JOIN: Returns all orders, even those without customer data
SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o 
ON c.id = o.customer_id;

I rarely use RIGHT JOIN — it’s usually clearer to flip the tables and use LEFT JOIN instead.

The Dreaded N+1 Query Problem

This is a classic mistake that haunts many junior developers:

-- First query: Get all customers
SELECT id, name FROM customers LIMIT 10;

-- Then for each customer, another query:
SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 2;
-- ... and so on

Instead, use a single JOIN:

-- One query to rule them all
SELECT 
    c.id,
    c.name,
    o.id as order_id,
    o.total
FROM customers c
LEFT JOIN orders o 
ON c.id = o.customer_id
WHERE c.id <= 10;

Window Functions: The Advanced Technique I Wish I’d Learned Sooner

Window functions are incredibly powerful but often overlooked. They let you perform calculations across a set of rows without needing a GROUP BY.

Running Totals

SELECT 
    id,
    created_at,
    total,
    SUM(total) OVER (ORDER BY created_at) as running_total
FROM orders
ORDER BY created_at;

Row Numbers and Ranking

-- Get the top 3 orders for each customer
SELECT 
    customer_id,
    total,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) as rank
FROM orders
WHERE rank <= 3;

Moving Averages

-- 7-day moving average of daily sales
SELECT 
    DATE(created_at) as date,
    SUM(total) as daily_total,
    AVG(SUM(total)) OVER (
        ORDER BY DATE(created_at) 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_average
FROM orders
GROUP BY DATE(created_at)
ORDER BY date;

Window functions replaced so many complex subqueries in my code. They’re cleaner, more readable, and often more performant.

Transaction Management: Don’t Be That Developer

Don’t be that developer that brings down a production system with a poorly managed transaction. As much as lunch is absolute importance to a developer, running a long-running query before heading for lunch can lock up all operations from completing, causing the system ground to a halt.

Keep Transactions Short

-- Bad: Long-running transaction
BEGIN;
SELECT * FROM huge_table WHERE complex_condition; -- Takes 5 minutes
UPDATE some_other_table SET status = 'processed';
COMMIT;

-- Good: Quick transaction
BEGIN;
UPDATE some_other_table SET status = 'processed' WHERE id = 123;
COMMIT;

Use Appropriate Isolation Levels

Most databases default to READ COMMITTED, which is fine for most use cases. But sometimes you need stronger guarantees:

-- For critical operations where consistency matters
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Your critical operations here
COMMIT;

Handle Deadlocks Gracefully

Deadlocks happen when two transactions are waiting for each other. The database will kill one of them, but your application should handle it gracefully:

-- In application code (pseudo-code)
max_retries = 3
for attempt in range(max_retries):
    try:
        execute_transaction()
        break
    except DeadlockError:
        if attempt == max_retries - 1:
            raise
        time.sleep(0.1 * (2 ** attempt))  # Exponential backoff

Data Types: The Details That Matter

Choosing the right data type isn’t just about storage — it affects performance, indexing, and query behavior.

Use the Smallest Data Type That Works

-- Don't use VARCHAR(255) for everything
CREATE TABLE users (
    id INT PRIMARY KEY,              -- Not BIGINT unless you need it
    name VARCHAR(100),               -- Not VARCHAR(255)
    email VARCHAR(320),              -- Maximum email length
    age TINYINT,                     -- 0-255, perfect for age
    is_active BOOLEAN,               -- Not TINYINT(1)
    created_at TIMESTAMP             -- Not DATETIME if you need timezone
);

Be Careful with NULLs

NULLs can cause unexpected behavior:

-- This might not work as expected
SELECT * FROM users WHERE age != 30;  -- Doesn't include NULL ages

-- Better to be explicit
SELECT * FROM users WHERE age != 30 OR age IS NULL;

Testing and Debugging: Save Your Future Self

Use EXPLAIN to Understand Query Plans

This is the most important debugging tool you have:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Learn to read execution plans. Look for:

• Seq Scan: Usually bad on large tables
• Index Scan: Good
• Nested Loop: Can be expensive with large datasets
• Hash Join: Usually efficient for large datasets

Test with Realistic Data

I once spent hours optimizing a query that ran perfectly on my 1000-row test dataset but failed miserably on production’s 10-million-row table. Always test with realistic data volumes.

-- Create test data that matches production scale
INSERT INTO orders (customer_id, total, created_at)
SELECT 
    (random() * 10000)::int,
    (random() * 1000)::decimal(10,2),
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

Common Pitfalls I’ve Learned to Avoid

1. The Implicit Conversion Trap

-- If user_id is a string, this forces a full table scan
SELECT * FROM users WHERE user_id = 123;

-- Better
SELECT * FROM users WHERE user_id = '123';

2. Functions in WHERE Clauses

-- This prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

-- This allows index usage
SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

3. Leading Wildcards in LIKE

-- This forces a full table scan
SELECT * FROM customers WHERE name LIKE '%john%';

-- This can use an index
SELECT * FROM customers WHERE name LIKE 'john%';

4. Correlated Subqueries

-- This runs the subquery for every row (slow)
SELECT * FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 5;

-- This is much faster
SELECT c.* FROM customers c
JOIN (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING COUNT(*) > 5
) o ON c.id = o.customer_id;

Database-Specific Optimizations

Different databases have different strengths. Here’s what I’ve learned about the major ones:

PostgreSQL

• Excellent support for JSON and array operations
• Great window functions and CTE support
• VACUUM and ANALYZE regularly for performance
• Use partial indexes for filtered queries

MySQL

• Be careful with implicit type conversions
• Use EXPLAIN FORMAT=JSON for detailed query plans
• Consider partitioning for very large tables
• InnoDB is almost always the right choice

SQL Server

• Great optimizer, trust it but verify with execution plans
• Use indexed views for complex aggregations
• Columnstore indexes for analytical workloads
• Always specify schema names (dbo.users, not just users)

The Monitoring and Maintenance Nobody Talks About

Writing good SQL isn’t just about the initial query — it’s about long-term maintenance.

Monitor Query Performance

Set up alerts for:

• Queries taking longer than expected
• Queries using too much CPU or memory
• Queries causing lock contention

Regular Maintenance

-- PostgreSQL
VACUUM ANALYZE table_name;

-- MySQL
OPTIMIZE TABLE table_name;

-- SQL Server
ALTER INDEX ALL ON table_name REBUILD;

Keep Statistics Updated

Query optimizers rely on statistics to make good decisions. Outdated statistics lead to poor query plans.

My Current SQL Workflow

After years of mistakes and learning, here’s my current approach to writing SQL:

1. Start simple: Write the basic query without optimization
2. Add indexes: Based on WHERE clauses and JOIN conditions
3. Test with realistic data: Not just the happy path
4. Check the execution plan: Use EXPLAIN or equivalent
5. Optimize: But only after measuring performance
6. Document: Especially complex queries and their purpose

The Mindset Shift That Changed Everything

The biggest change in my SQL journey wasn’t learning new syntax or techniques — it was changing my mindset from “make it work” to “make it work reliably at scale.”

Every query I write now, I ask myself:

• How will this perform with 10x more data?
• What happens if this index gets dropped?
• Can someone else understand this query in six months?
• What’s the worst-case scenario for this query?

This mindset shift has saved me from countless production issues and made me a much better developer.

Final Thoughts

SQL is deceptively simple. The basic syntax is straightforward, but writing efficient, maintainable queries that perform well at scale is an art that takes years to master. I’m still learning new things after years of writing SQL professionally.

The key is to learn from mistakes — both your own and others’. Every slow query is a learning opportunity. Every production incident teaches you something new about database behavior.

Don’t be afraid to experiment, but do it safely. Use transactions, test with realistic data, and always have a rollback plan. The database is not your enemy — it’s a powerful tool that rewards understanding and punishes carelessness.

Most importantly, remember that readable code is maintainable code. Your future self (and your teammates) will thank you for taking the time to write clear, well-structured SQL that tells a story about what you’re trying to accomplish.

What’s your worst SQL disaster story? Have you learned any hard lessons about database performance the hard way? I’d love to hear your experiences in the comments — we all learn from each other’s mistakes.