Answer.

Background:

Nested subqueries were originally designed in SQL to enhance the expressive capabilities of the language and to solve complex business problems that do not fit into simple SELECT statements. However, with the growth of data volumes and the complexity of relational models came the realization that nested subqueries do not always work efficiently: much depends on the optimizer's implementation of the specific DBMS.

Problem:

The main challenge is finding a balance between readability, correctness of logic, and performance. A nested subquery is not always optimized to join operations and often becomes a costly traversal loop (Nested Loops).

Solution:

Use nested subqueries in SELECT for computing aggregates or complex expressions when there is no alternative.
For large volumes of data, switch to JOIN — this allows the optimizer to make the query set-based and apply indexes.
Pull the subquery to the outer part (via WITH/CTE) if you need to improve readability or performance.

Example code:

-- Nested subquery in SELECT (carefully!)
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.client_id = clients.id) AS order_count
FROM clients;

-- Equivalent through JOIN (usually faster):
SELECT clients.name, COUNT(orders.id) AS order_count
FROM clients
LEFT JOIN orders ON orders.client_id = clients.id
GROUP BY clients.name;

Key features:

Correlated nested subqueries often yield a runtime of O(N*M)
Uncorrelated subqueries are safer and faster
Moving the subquery to WITH/CTE or JOIN increases predictability of the execution plan and speeds up execution

Trick questions.

Does a nested subquery in SELECT run faster than an equivalent LEFT JOIN?

Most often — no. A correlated subquery in SELECT is executed for each row of the outer query, while JOIN is built once with indexes for the entire table.

Can a subquery be used in FROM instead of CTE (WITH), and will there be a difference?

Yes, a subquery in FROM:**

SELECT t1.id, sub.agg
FROM table1 t1
JOIN (SELECT id, MAX(val) AS agg FROM table2 GROUP BY id) sub ON t1.id = sub.id;

But CTE sometimes provides greater readability and may lead to different optimization in execution plans.

Are all nested subqueries optimized to equivalent JOINs?

No. Not all DBMS are able to do this evenly; sometimes a nested subquery leads to scanning each row, especially if there is correlation between the outer and inner queries.

Typical mistakes and anti-patterns

Using nested correlated subqueries in SELECT with large volumes.
Duplicating filtering conditions in both the inner and outer queries.
Attempting to replace all nested subqueries with JOINs without analyzing the tasks.

Real-life example

Negative case

A sales manager generated a report on clients, counting the number of orders using an inner SELECT. Execution times were in minutes, and server load increased exponentially.

Pros:

Clear logic Cons:
Very slow report on a large number of clients
Inefficient server load

Positive case

The query was rewritten with LEFT JOIN and grouping.