10 SQL Anti-Patterns You Must Avoid in Production

10 SQL Anti-Patterns You Must Avoid in Production

“Slow SQL isn’t always about bad servers — it’s often about bad habits.”

As SQL developers and data engineers, we often prioritize functionality and overlook query quality. But poor SQL habits can lead to:

• Long response times
• Bottlenecked applications
• Excessive I/O and CPU
• Poor scalability

In this post, we’ll break down 10 critical SQL anti-patterns and how to fix them.

❌ 1. N+1 Query Pattern

Problem: Querying in a loop for each record.

-- BAD: Fetching orders per customer inside loop
SELECT * FROM Customers;
-- For each customer:
SELECT * FROM Orders WHERE customer_id = ?;

✅ Fix: Join and aggregate in one query

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

❌ 2. Wildcard Index Scans

Problem: Leading wildcard disables index usage.

SELECT * FROM Products WHERE name LIKE '%phone';

✅ Fix: Use full-text search or structured LIKE

-- Better (index usable)
SELECT * FROM Products WHERE name LIKE 'phone%';

❌ 3. Implicit Data Type Conversions

Problem: Filtering numeric column with a string.

SELECT * FROM Orders WHERE id = '123';

✅ Fix: Match types explicitly

SELECT * FROM Orders WHERE id = 123;

🔍 Use query plans to detect implicit conversion overhead.

❌ 4. Scalar Subqueries in SELECT

Problem: Executing subquery for every row.

SELECT id, (SELECT COUNT(*) FROM OrderItems WHERE order_id = o.id)
FROM Orders o;

✅ Fix: Use join and aggregation

SELECT o.id, COUNT(oi.id) AS item_count
FROM Orders o
LEFT JOIN OrderItems oi ON oi.order_id = o.id
GROUP BY o.id;

❌ 5. SELECT * in Production

Problem: Fetches unnecessary columns, causes bloat.

SELECT * FROM Transactions;

✅ Fix: Select only what you need

SELECT id, amount, transaction_date FROM Transactions;

❌ 6. Redundant DISTINCT

Problem: Using DISTINCT to patch bad joins.

SELECT DISTINCT name FROM Customers c JOIN Orders o ON o.customer_id = c.id;

✅ Fix: Analyze join logic and duplicates instead.

❌ 7. Missing WHERE Clause in DELETE/UPDATE

-- Danger zone!
DELETE FROM Users;
UPDATE Orders SET status = 'Shipped';

✅ Fix: Always qualify with a WHERE clause.

DELETE FROM Users WHERE is_deleted = true;

❌ 8. No Index on Foreign Keys

Problem: FK lookups scan entire referenced table.

-- Missing index on Orders.customer_id

✅ Fix: Add supporting indexes

CREATE INDEX idx_orders_customer ON Orders(customer_id);

❌ 9. Overusing OR instead of UNION ALL

SELECT * FROM Orders WHERE status = 'pending' OR status = 'shipped';

✅ Fix: Separate indexed paths with UNION ALL

SELECT * FROM Orders WHERE status = 'pending'
UNION ALL
SELECT * FROM Orders WHERE status = 'shipped';

❌ 10. Not Using ANALYZE or EXPLAIN

Problem: Blindly writing queries without measuring impact.
✅ Fix: Always inspect query plans

EXPLAIN ANALYZE SELECT ...

Final Thoughts: Write It Once, Run It Well

Avoiding anti-patterns isn't just about style — it's about performance, reliability, and cost.

"SQL is declarative. Let the engine help — but don’t tie its hands with bad habits."

#SQL #Performance #AntiPatterns #QueryOptimization #BestPractices #AdvancedSQL #DataEngineering