Why Subqueries Can Be Slow

It's **Day 49**. Subqueries are powerful, but they are also a common cause of performance issues.

The Problem: The Nested Loop

Think of a subquery in a `WHERE` clause like a loop inside a loop in Python or Javascript.

-- For every row in USERS (Loop 1)

SELECT * FROM users u

WHERE u.id IN (

-- Run this query (Loop 2)

SELECT user_id FROM orders

);

If both tables have 1 million rows, the computer is trying to do a massive amount of work for every single user.

Common Performance Killers

1. **Un-indexed subqueries**: If the column in the subquery isn't indexed, the DB has to scan the whole table over and over.

2. **Scalar subqueries in SELECT**: `SELECT name, (SELECT count(*) ...) FROM customers`. This is extremely slow because it's effectively a query for every row in your result set.

The Solution

Tomorrow, we'll learn about **CTEs**, which act as a modern, cleaner alternative to nested subqueries and often help the database optimize the search better.

Your Task for Today

Explain to a coworker (or a rubber duck) why a correlated subquery is usually slower than a standard join.

*Day 50: Common Table Expressions (CTEs) Explained—The Senior engineer's way.*