SQL Query Anti-Patterns: Non-Sargable Queries, N+1, Cartesian Joins and SELECT *

A query can return the right answer and still be wrong for production. These SQL anti-patterns stop the database using indexes, multiply rows by accident or flood the network, and "what's wrong with this query?" is a favourite senior interview format. Each one below shows the bad version, why it hurts and the fix.

Spotting SQL Anti-Patterns

Non-Sargable Predicates (Index-Killer Queries)

Anti-Pattern 1: Function on indexed column in WHERE

-- ❌ WRONG: YEAR() wraps hire_date — cannot use index on hire_date
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- ✅ RIGHT: Range condition uses index
SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

-- ❌ WRONG: arithmetic on indexed column
SELECT * FROM products WHERE price * 1.18 > 1000;

-- ✅ RIGHT: move calculation to the other side
SELECT * FROM products WHERE price > 1000 / 1.18;

-- ❌ WRONG: UPPER() on indexed email column
SELECT * FROM users WHERE UPPER(email) = 'NAVEED@EXAMPLE.COM';

-- ✅ RIGHT: use case-insensitive collation or store normalised
SELECT * FROM users WHERE email = 'naveed@example.com';
-- Or: create functional index: CREATE INDEX idx_upper_email ON users ((UPPER(email)));

Anti-Pattern 2: Leading wildcard in LIKE

-- ❌ WRONG: Leading % forces full table scan
SELECT * FROM employees WHERE name LIKE '%Kumar';

-- ✅ BETTER: trailing wildcard uses index
SELECT * FROM employees WHERE name LIKE 'Kumar%';

-- ✅ BEST: If you need substring search, use FULL-TEXT index
ALTER TABLE employees ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM employees WHERE MATCH(name) AGAINST ('Kumar' IN NATURAL LANGUAGE MODE);

Anti-Pattern 3: Implicit Type Conversion

-- ❌ WRONG: emp_id is INT but we compare to a STRING
SELECT * FROM employees WHERE emp_id = '42';
-- MySQL silently converts '42' to 42 — but if column is VARCHAR:
-- The string column gets cast to INT for comparison → full scan!

-- ✅ RIGHT: match types explicitly
SELECT * FROM employees WHERE emp_id = 42;

-- ❌ WRONG: date string without quotes
SELECT * FROM orders WHERE order_date = 2025-01-15;
-- MySQL reads this as 2025 - 1 - 15 = 2009! (arithmetic, not a date)

-- ✅ RIGHT: quote date strings
SELECT * FROM orders WHERE order_date = '2025-01-15';

The N+1 Query Problem

-- ❌ N+1 PROBLEM (pseudocode showing what bad application code does):
-- Query 1: SELECT customer_id, name FROM customers LIMIT 100;
-- Then for each of the 100 customers, run separately:
-- Query 2:   SELECT * FROM orders WHERE customer_id = 1;
-- Query 3:   SELECT * FROM orders WHERE customer_id = 2;
-- ... 100 more queries!  Total: 101 database round trips

-- ✅ FIX: One JOIN instead of 101 queries
SELECT c.customer_id, c.name,
       COUNT(o.order_id)  AS order_count,
       SUM(o.amount)      AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- ✅ Alternative fix: IN clause (if ORM forces it)
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, ... 100);  -- one query with 100 IDs

Cartesian Product Explosion

-- ❌ WRONG: Missing JOIN condition creates cartesian product
SELECT e.name, d.dept_name
FROM employees e, departments d;
-- If 100 employees × 10 departments = 1000 rows! (not 100)

-- ✅ RIGHT: Always specify JOIN condition
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- ❌ WRONG: CROSS JOIN on large tables accidentally
SELECT * FROM fact_sales CROSS JOIN dim_date;
-- 1M sales × 3650 dates = 3.65 BILLION rows!

SELECT * in Production

-- ❌ WRONG: SELECT * problems:
-- 1. Fetches unused columns → more network traffic
-- 2. Cannot use covering index
-- 3. Breaks if schema changes (new column appears in unexpected position)
-- 4. In JOINs, ambiguous column names can cause errors
SELECT * FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
-- Now which table does "name" come from? Ambiguous!

-- ✅ RIGHT: Specify exactly what you need
SELECT e.emp_id, e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Reading Query Execution Plans (EXPLAIN)

-- MySQL: EXPLAIN shows how a query will be executed
EXPLAIN SELECT * FROM employees WHERE city = 'Bangalore';
-- Look for "type" column:
--   ALL = full table scan (bad!)
--   index = full index scan (ok)
--   range = index range scan (good)
--   ref = index lookup (good)
--   const = single row lookup (best!)

-- EXPLAIN ANALYZE (PostgreSQL) - actually runs the query and shows real costs
EXPLAIN ANALYZE SELECT * FROM employees WHERE city = 'Bangalore';

-- MySQL: EXPLAIN FORMAT=JSON for detailed output
EXPLAIN FORMAT=JSON SELECT e.name, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

FAQs

What does sargable mean?

Search-ARGument-ABLE: a predicate the database can answer using an index, such as order_date >= '2025-01-01'. Wrapping the column in a function, like YEAR(order_date) = 2025, usually prevents index use.

What is the N+1 query problem?

Running one query to fetch a list and then one more query per item. Replace it with a single JOIN or an IN query that fetches all related rows at once.

Why avoid SELECT * in production code?

It reads and sends columns you don't need, prevents covering-index plans, and breaks code silently when columns are added or reordered.