SQL Anti-Patterns: 12 Mistakes That Slow Queries or Break Results

A query can return fast and still be wrong. A query can return correct rows and still scan 80 million of them to find ten. Both failures trace back to a small set of repeatable SQL anti-patterns. Most of them pass code review because the syntax is valid and the test dataset is small. This guide covers 12 anti-patterns, grouped by whether they hurt performance or correctness, with a fix for each and a script to find them in production.

Quick Takeaways

  • Non-sargable predicates (functions on columns, leading wildcards, implicit casts) stop the optimizer from using indexes. Rewrite the predicate, not the index.
  • NULL semantics and join logic cause silent wrong answers. NOT IN, LEFT JOIN plus a WHERE filter, and DISTINCT over fan-out joins are the usual culprits.
  • Pagination and row-by-row access patterns (OFFSET, N+1) degrade linearly with data size. Use keyset pagination and set-based queries.
  • Verify every fix with EXPLAIN (ANALYZE, BUFFERS) rather than intuition.
Category Anti-Patterns Typical Symptom
Index defeat #2, #3, #4 Seq Scan on indexed column
Excess work #1, #5, #6, #7, #8, #12 High I/O, slow at scale
Wrong results #9, #10, #11 Missing or duplicated rows

Code examples use PostgreSQL syntax. The principles apply to MySQL, SQL Server, and other engines, with small dialect differences.

Performance Anti-Patterns

1. Using SELECT * in Production Queries

SELECT * pulls every column, including wide TEXT/JSONB fields. It blocks index-only scans, inflates network transfer, and breaks downstream code when schemas change.

-- Anti-pattern
SELECT * FROM orders WHERE customer_id = 4821;

-- Fix: name the columns you need; a covering index can now serve the query
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 4821;

-- Covering index (PostgreSQL 11+)
CREATE INDEX idx_orders_cust_cover
ON orders (customer_id) INCLUDE (order_date, total_amount);

With the covering index, the planner can return an Index Only Scan and skip the heap entirely.

2. Wrapping Indexed Columns in Functions

A predicate is sargable (Search ARGument ABLE) when the engine can use an index seek on it. Applying a function to the column forces a full scan, because the index stores raw values, not function outputs.

-- Anti-pattern: index on order_date is ignored
SELECT order_id FROM orders
WHERE DATE_TRUNC('month', order_date) = '2026-10-01';

-- Fix: rewrite as a range on the raw column
SELECT order_id FROM orders
WHERE order_date >= '2026-10-01'
  AND order_date <  '2026-11-01';

If you cannot rewrite the predicate, build an expression index: CREATE INDEX ON users (LOWER(email));.

3. Leading Wildcards in LIKE

LIKE '%smith' cannot use a B-tree index because the search has no fixed prefix. LIKE 'smith%' can.

Pattern B-tree Index Usable Alternative
LIKE 'smith%' Yes None needed
LIKE '%smith' No Index on REVERSE(col)
LIKE '%smith%' No pg_trgm GIN index or full-text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_name_trgm
ON customers USING gin (name gin_trgm_ops);
-- '%smith%' now uses the trigram index

4. Implicit Type Conversions

Comparing a VARCHAR column to a numeric literal (or mismatched types in a join) makes the engine cast every row. The index on the column goes unused.

-- Anti-pattern: phone is VARCHAR, literal is INT
SELECT * FROM contacts WHERE phone = 5551234567;

-- Fix: match the column type
SELECT contact_id FROM contacts WHERE phone = '5551234567';

Check join keys too. A BIGINT joined to an INT or a UUID stored as text produces silent casts. In SQL Server, the plan shows a CONVERT_IMPLICIT warning.

5. Deep OFFSET Pagination

OFFSET 500000 LIMIT 20 reads and discards 500,000 rows before returning 20. Cost grows linearly with page depth. Keyset pagination seeks directly to the position.

-- Anti-pattern
SELECT order_id, created_at FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 500000;

-- Fix: keyset (pass the last row's values from the previous page)
SELECT order_id, created_at FROM orders
WHERE (created_at, order_id) < ('2026-09-14 10:22:01', 981234)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
Method Page 1 Cost Page 25,000 Cost Random Page Jump Stable Under Inserts
OFFSET Low Very high Yes No
Keyset Low Low No Yes

Back the keyset query with a composite index on (created_at DESC, order_id DESC).

6. Correlated Subqueries in the SELECT List

A correlated subquery can execute once per outer row. Modern optimizers sometimes flatten it, but you cannot rely on that.

-- Anti-pattern: runs a lookup per customer
SELECT c.customer_id,
       (SELECT SUM(o.total_amount)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS lifetime_value
FROM customers c;

-- Fix: aggregate once, then join
SELECT c.customer_id, COALESCE(o.lifetime_value, 0) AS lifetime_value
FROM customers c
LEFT JOIN (
    SELECT customer_id, SUM(total_amount) AS lifetime_value
    FROM orders
    GROUP BY customer_id
) o USING (customer_id);

7. The N+1 Query Problem

The application fetches a list, then fires one query per item. At 1,000 items, that is 1,001 round trips. Network latency dominates, not query time.

# Anti-pattern: 1 + N queries
orders = conn.execute("SELECT order_id FROM orders LIMIT 1000").fetchall()
for (order_id,) in orders:
    items = conn.execute(
        "SELECT * FROM order_items WHERE order_id = %s", (order_id,)
    ).fetchall()

# Fix: one set-based query using ANY() with a parameter array
order_ids = [row[0] for row in orders]
items = conn.execute(
    """
    SELECT order_id, product_id, quantity
    FROM order_items
    WHERE order_id = ANY(%s)
    """,
    (order_ids,),
).fetchall()

In ORMs, look for eager loading options (selectinload in SQLAlchemy, includes in Rails, select_related in Django).

8. Filtering Rows in HAVING Instead of WHERE

HAVING filters after aggregation. Conditions that do not depend on aggregates belong in WHERE, where they cut rows before the expensive grouping step.

-- Anti-pattern: groups all regions, then discards
SELECT region, SUM(revenue) AS total
FROM sales
GROUP BY region
HAVING region IN ('EMEA', 'APAC');

-- Fix: filter first
SELECT region, SUM(revenue) AS total
FROM sales
WHERE region IN ('EMEA', 'APAC')
GROUP BY region;

Reserve HAVING for aggregate conditions such as HAVING SUM(revenue) > 10000.

Correctness Anti-Patterns

9. NOT IN with Nullable Columns

If the subquery returns even one NULL, NOT IN evaluates to UNKNOWN for every row and returns zero results. This bug is silent.

-- Anti-pattern: returns nothing if any blocked.customer_id is NULL
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM blocked);

-- Fix: NOT EXISTS handles NULLs correctly and optimizes to an anti-join
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM blocked b WHERE b.customer_id = c.customer_id
);

The same logic explains why col = NULL never matches. Use IS NULL, or IS NOT DISTINCT FROM for null-safe equality.

10. Filtering the Right Table of a LEFT JOIN in WHERE

A WHERE condition on the right-side table discards the NULL rows the LEFT JOIN produced. The query silently becomes an INNER JOIN.

-- Anti-pattern: customers with no 2026 orders vanish
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

-- Fix: move the condition into the ON clause
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-01-01';

11. Masking Join Fan-Out with DISTINCT

Joining two one-to-many tables multiplies rows. Adding DISTINCT hides the duplicates but not the damage. Aggregates computed over the inflated rows are wrong, and the sort or hash for DISTINCT is expensive.

-- Anti-pattern: each order repeats once per payment, so SUM is inflated
SELECT o.customer_id, SUM(oi.price * oi.qty) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments p     ON p.order_id  = o.order_id
GROUP BY o.customer_id;

-- Fix: pre-aggregate each branch to one row per order, then join
WITH items AS (
    SELECT order_id, SUM(price * qty) AS item_total
    FROM order_items GROUP BY order_id
),
pay AS (
    SELECT order_id, SUM(amount) AS paid
    FROM payments GROUP BY order_id
)
SELECT o.customer_id, SUM(i.item_total) AS revenue, SUM(pay.paid) AS paid
FROM orders o
JOIN items i  ON i.order_id = o.order_id
LEFT JOIN pay ON pay.order_id = o.order_id
GROUP BY o.customer_id;

Rule of thumb: if you reach for DISTINCT to fix duplicates, find the join that created them.

12. Using UNION When UNION ALL Is Enough

UNION deduplicates, which forces a sort or hash across the entire combined result. If the inputs cannot overlap, that work is wasted.

-- Anti-pattern: partitions never overlap, but dedup still runs
SELECT * FROM sales_2025
UNION
SELECT * FROM sales_2026;

-- Fix
SELECT order_id, amount FROM sales_2025
UNION ALL
SELECT order_id, amount FROM sales_2026;

Real-World Scenarios

Scenario 1: Dashboard timeouts on an e-commerce orders table (180M rows). A “monthly revenue” panel used DATE_TRUNC on order_date (#2) inside a correlated subquery (#6). Rewriting it as a range predicate plus a pre-aggregated join let the planner use the date index. The Seq Scan became an Index Range Scan, cutting runtime from minutes to under a second.

Scenario 2: Churn report undercounts inactive customers. An analyst joined customers to subscriptions with a LEFT JOIN, then filtered WHERE subscriptions.status = 'cancelled' (#10). Customers who never subscribed disappeared from the denominator. Moving the filter to ON restored them, and the churn rate corrected itself.

Scenario 3: Inflated revenue in a finance export. A report joined orders to line items and shipments, then wrapped everything in DISTINCT (#11). Totals were off by 20–40% on multi-shipment orders. Pre-aggregating each child table fixed both accuracy and runtime.

Detect Anti-Patterns in Production

Do not guess which queries hurt. Rank them with pg_stat_statements, then run EXPLAIN (ANALYZE, BUFFERS) on the worst offenders.

import psycopg  # psycopg 3

TOP_QUERIES_SQL = """
SELECT
    LEFT(query, 120)                         AS query_snippet,
    calls,
    ROUND(total_exec_time::numeric, 1)       AS total_ms,
    ROUND(mean_exec_time::numeric, 2)        AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
"""

def top_slow_queries(dsn: str) -> list[tuple]:
    """Return the 15 queries consuming the most cumulative execution time."""
    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(TOP_QUERIES_SQL)
            return cur.fetchall()

def explain(dsn: str, query: str) -> str:
    """Run EXPLAIN ANALYZE; inspect output for Seq Scan, Sort spills, and row-estimate gaps."""
    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(f"EXPLAIN (ANALYZE, BUFFERS) {query}")
            return "\n".join(row[0] for row in cur.fetchall())

if __name__ == "__main__":
    for row in top_slow_queries("postgresql://user:pass@localhost/mydb"):
        print(row)

Enable the extension first with CREATE EXTENSION pg_stat_statements; and add it to shared_preload_libraries. Sort by total_exec_time to find cumulative cost, and by mean_exec_time to find individually slow statements. A high-call, low-latency query (typical of N+1) shows up only in the first ranking.

In plans, watch for these signals:

  • Seq Scan on a large table with a selective filter points to anti-patterns #2, #3, #4.
  • Rows Removed by Filter in the millions points to #5 or #8.
  • Estimated rows vs. actual rows off by 10x or more means stale statistics. Run ANALYZE.
  • Sort Method: external merge means the sort spilled to disk, often caused by #11 or #12.

FAQ

What is a SQL anti-pattern?

A SQL anti-pattern is a query design that looks valid but degrades performance or produces incorrect results. Common examples are SELECT *, functions on indexed columns, and NOT IN against nullable subqueries.

Why is my query slow even though the column has an index?

The predicate is probably non-sargable. Functions on the column, leading wildcards in LIKE, and implicit type casts all prevent index use. Rewrite the condition so the raw column is compared directly, or create an expression index.

Is SELECT DISTINCT bad for performance?

It adds a sort or hash aggregation over the full result. It also hides join fan-out bugs that corrupt aggregates. Use it when you truly need unique values, not to repair duplicates from a faulty join.

How do I find slow SQL queries in production?

Enable pg_stat_statements (PostgreSQL), Query Store (SQL Server), or the slow query log with Performance Schema (MySQL). Rank by total execution time, then inspect plans with EXPLAIN ANALYZE.

Hot this week

Query Optimization: Indexes, Execution Plans, and Faster SQL

Learn SQL query optimization with indexes, EXPLAIN ANALYZE, and rewrite patterns. Copy-ready PostgreSQL code included. Speed up your slow queries today.

How Uber, Lyft, and Airbnb Use Data for Dynamic Pricing

See how Uber, Lyft, and Airbnb turn demand data into dynamic prices, with Python code for surge and booking models. Build your own pricing engine.

Pivoting and Unpivoting Data in SQL

Learn how to pivot and unpivot data in SQL with PIVOT, UNPIVOT, CASE, and UNNEST. Copy-paste code for SQL Server, PostgreSQL, and Snowflake. Start now.

GDPR for Data Scientists: Your Responsibilities Explained

Learn what GDPR requires from data scientists: lawful basis, minimization, pseudonymization, DSARs, Article 22. Get code and a checklist.

Funnel Analysis in SQL: Find Where Users Drop Off

Learn funnel analysis in SQL with copy-paste queries for ordered steps, drop-off rates, and segments. Build your conversion funnel today.

Topics

Query Optimization: Indexes, Execution Plans, and Faster SQL

Learn SQL query optimization with indexes, EXPLAIN ANALYZE, and rewrite patterns. Copy-ready PostgreSQL code included. Speed up your slow queries today.

How Uber, Lyft, and Airbnb Use Data for Dynamic Pricing

See how Uber, Lyft, and Airbnb turn demand data into dynamic prices, with Python code for surge and booking models. Build your own pricing engine.

Pivoting and Unpivoting Data in SQL

Learn how to pivot and unpivot data in SQL with PIVOT, UNPIVOT, CASE, and UNNEST. Copy-paste code for SQL Server, PostgreSQL, and Snowflake. Start now.

GDPR for Data Scientists: Your Responsibilities Explained

Learn what GDPR requires from data scientists: lawful basis, minimization, pseudonymization, DSARs, Article 22. Get code and a checklist.

Funnel Analysis in SQL: Find Where Users Drop Off

Learn funnel analysis in SQL with copy-paste queries for ordered steps, drop-off rates, and segments. Build your conversion funnel today.

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Related Articles

Popular Categories