What are the advantages and disadvantages of using nested subqueries in a SELECT statement?

Nested subqueries in the SELECT statement allow executing queries within other queries, which helps solve complex data retrieval tasks.

Advantages:

• Enable writing compact and logically related queries.
• Simplify reading and understanding when subqueries reflect separate logical parts.
• Can be used for filtering or calculating values based on other tables.

Disadvantages:

• Can negatively impact performance, especially if subqueries are not optimized or executed for each row of the main query.
• Sometimes harder to optimize than equivalent JOIN queries.
• In some DBMS, nested subqueries can lead to increased execution time.

Example:

SELECT employee_name, (SELECT department_name FROM departments WHERE departments.id = employees.department_id) AS dept_name
FROM employees;

Here, the subquery inside SELECT retrieves the department name for each employee.