N+1 queries and how to fix them with SQL

N+1 Is One Query Followed by N More

N+1 is the most common performance bug in applications using an ORM. You fetch N rows, then loop and fire one query per row to get related data. 100 orders → 1 query for orders + 100 queries for customer names = 101 database round trips.

-- The N+1 pattern (pseudocode)
orders = db.query("SELECT * FROM orders LIMIT 100")
for order in orders:
    customer = db.query("SELECT * FROM customers WHERE id = " + order.customer_id)
    # 100 extra queries

Fix 1: JOIN in the Original Query

-- One query, one round trip
SELECT o.id, o.amount, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
LIMIT 100;

Fix 2: Batch with IN When JOIN Is Not Practical

-- Fetch orders first, collect IDs, fetch all customers at once
SELECT * FROM customers
WHERE id IN (1, 5, 12, 34, 67);  -- all unique customer_ids from the orders result

ORMs solve this with eager loading (include or preload methods). Under the hood, they use either a JOIN or the IN-batch pattern. Understanding the SQL makes you a better ORM user.