10 Common SQL Anti-Patterns and How to Fix Them

What Is a SQL Anti-Pattern?

A SQL anti-pattern is a common approach that seems reasonable at first but consistently causes problems — performance degradation, data integrity issues, or maintenance nightmares. Unlike bugs, anti-patterns often work correctly; they just work badly at scale or under edge conditions.

This guide covers the 10 most damaging SQL anti-patterns, with concrete before-and-after examples for each.

1. SELECT * in Production Queries

The problem: SELECT * reads every column in the table, even those your query doesn't need. In a columnar store like BigQuery, this multiplies scan costs. In row-based databases, it prevents index-only scans and increases network payload.

-- Anti-pattern
SELECT * FROM orders WHERE status = 'completed';

-- Fix: list only the columns you need
SELECT order_id, customer_id, amount, created_at
FROM orders
WHERE status = 'completed';

Why it matters: A table with 40 columns where you only need 4 means reading 10x more data than necessary.

2. Functions on Indexed Columns in WHERE Clauses

The problem: Wrapping a column in a function prevents the query engine from using an index on that column. The result is a full table scan instead of an index seek.

-- Anti-pattern: function on column breaks index usage
WHERE YEAR(created_at) = 2024
WHERE UPPER(email) = '[email protected]'
WHERE CONVERT(VARCHAR, order_id) = '12345'

-- Fix: rewrite to keep the column bare
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE email = '[email protected]'  -- store emails in lowercase
WHERE order_id = 12345

3. Implicit Type Conversions

The problem: Comparing columns of different data types forces the engine to convert one side, often making the index unusable and introducing subtle bugs.

-- Anti-pattern: customer_id is INT, '12345' is VARCHAR
WHERE customer_id = '12345'

-- Anti-pattern: date comparison with string
WHERE order_date = '2024-01-15'  -- depends on session date format

-- Fix: match data types explicitly
WHERE customer_id = 12345
WHERE order_date = CAST('2024-01-15' AS DATE)

4. N+1 Query Pattern

The problem: Executing one query to get a list, then one query per row to get related data. This pattern is common when application code loops over results and issues individual queries.

-- Anti-pattern: 1 query for orders + N queries for customer names
SELECT order_id, customer_id FROM orders WHERE status = 'completed';
-- Then for each row: SELECT name FROM customers WHERE id = ?

-- Fix: JOIN once
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed';

Impact: 1,000 orders = 1,001 queries instead of 1.

5. Using NOT IN with Subqueries Containing NULLs

The problem: NOT IN returns no rows if the subquery contains any NULL values, because x NOT IN (1, 2, NULL) evaluates to NULL (unknown), not TRUE.

-- Anti-pattern: silently returns 0 rows if deleted_at has any NULLs
SELECT id FROM users
WHERE id NOT IN (SELECT user_id FROM deleted_accounts);

-- Fix: use NOT EXISTS
SELECT id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM deleted_accounts d WHERE d.user_id = u.id
);

-- Or: use LEFT JOIN / IS NULL
SELECT u.id FROM users u
LEFT JOIN deleted_accounts d ON d.user_id = u.id
WHERE d.user_id IS NULL;

6. Storing Comma-Separated Values in a Single Column

The problem: Storing multiple values in one column (e.g., tags = 'sql,performance,bigquery') violates first normal form and makes querying, indexing, and joining impossible without string manipulation.

-- Anti-pattern
CREATE TABLE articles (
  id INT,
  tags VARCHAR(500)  -- 'sql,performance,bigquery'
);

-- Fix: use a junction table
CREATE TABLE article_tags (
  article_id INT,
  tag VARCHAR(50),
  PRIMARY KEY (article_id, tag)
);

7. Using DISTINCT to Hide Duplicate Bugs

The problem: Adding DISTINCT to suppress duplicate rows often masks a JOIN problem (usually a missing join condition or a one-to-many relationship producing fan-out). It also forces a sort operation.

-- Anti-pattern: DISTINCT hiding a cartesian product
SELECT DISTINCT o.order_id, o.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id;
-- This produces one row per order_item, then deduplicates

-- Fix: understand the relationship and query correctly
SELECT o.order_id, o.amount,
       COUNT(oi.id) AS item_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.amount;

8. Unbounded UPDATE or DELETE Without a WHERE Clause

The problem: A missing WHERE clause on UPDATE or DELETE affects every row in the table. This is one of the most common causes of production data disasters.

-- Anti-pattern: updates every row
UPDATE orders SET status = 'cancelled';

-- Anti-pattern: deletes every row
DELETE FROM sessions;

-- Fix: always include a WHERE clause; test with SELECT first
SELECT COUNT(*) FROM orders WHERE created_at < '2023-01-01';
-- Then:
DELETE FROM orders WHERE created_at < '2023-01-01';

Best practice: Wrap destructive operations in a transaction and verify the row count before committing.

9. Using ORDER BY in Subqueries

The problem: ORDER BY in a subquery or CTE has no guaranteed effect on the outer query's result order. The SQL standard does not preserve subquery ordering, and most engines ignore it.

-- Anti-pattern: ORDER BY in subquery is meaningless
SELECT * FROM (
  SELECT * FROM orders ORDER BY created_at DESC
) sub
WHERE status = 'completed';

-- Fix: apply ORDER BY only in the outermost query
SELECT * FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC;

10. Hardcoding Magic Numbers and Strings

The problem: Embedding literal values directly in queries makes code brittle, hard to maintain, and prone to inconsistency when the value changes.

-- Anti-pattern
WHERE status = 3  -- what does 3 mean?
WHERE region_id = 42
WHERE discount_rate > 0.15

-- Fix: use named constants, lookup tables, or parameters
WHERE status = (SELECT id FROM order_statuses WHERE name = 'completed')
WHERE region_id = @RegionId  -- parameterised
WHERE discount_rate > @MaxDiscountThreshold

Summary

|---|---|---|

SQL Querywise Reviewer automatically detects all 10 of these anti-patterns and provides specific, actionable recommendations with severity ratings — saving hours of manual code review.

*SQL Querywise Reviewer checks your SQL against 53 enterprise best practices, including all anti-patterns listed above, and flags issues with severity ratings and fix suggestions.*