How to optimize a query with subqueries for better readability and performance?

To optimize queries with subqueries and improve readability and performance, the following approaches can be used:

1. Using JOIN instead of subqueries — subqueries can often be rewritten using JOIN, which allows the database to optimize execution better.

2. CTE (Common Table Expressions) — using WITH to define intermediate results makes queries more readable and sometimes helps the optimizer.

3. Indexing — ensure that fields involved in subquery conditions and joins are indexed.

4. Avoiding correlated subqueries — they execute for each row and significantly reduce performance.

5. Breaking down complex queries — sometimes it's better to split one complex query into several simple ones while preserving intermediate results.

Example of rewriting a subquery with JOIN:

-- Subquery
SELECT e.name, e.department_id
FROM employees e
WHERE e.department_id IN (SELECT d.id FROM departments d WHERE d.location = 'NY');

-- Rewritten with JOIN
SELECT e.name, e.department_id
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = 'NY';

This approach improves readability and often performance.