Subqueries in the SELECT Clause: The Silent Performance Killer

Welcome to **Day 57**. Today is about efficiency. Have you ever written something like this?

SELECT

u.name,

(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count

FROM users u;

Why this is dangerous

This is called a **Scalar Subquery**. For every single user in your database, your server has to run a completely separate query to count their orders.

This will crawl to a halt very quickly.

The Professional Alternative: LEFT JOIN + GROUP BY

SELECT

u.name,

COUNT(o.id) AS order_count

FROM users u

LEFT JOIN orders o ON u.id = o.user_id

GROUP BY u.name;

This single query accomplishes the exact same thing but in a way that the database can optimize massively.

When is it okay?

Scalar subqueries are only okay when you are returning a **constant** value that doesn't change per row (e.g., `SELECT name, (SELECT MAX(price) FROM products) FROM items`).

Your Task for Today

Look for a scalar subquery in your code and refactor it into a Join.

*Day 58: LATERAL Joins vs Subqueries (Next Level).*