? Nested Query (Subquery) in SQL

A Nested Query or Subquery is a query inside another SQL query. The inner query runs first, and its result is used by the outer query.

? Syntax:

SELECT column1, column2FROM table1WHERE column3 IN (    SELECT column3    FROM table2    WHERE condition);

? Example:

Get employees who work in departments located in 'New York':

SELECT employee_nameFROM employeesWHERE department_id IN (    SELECT department_id    FROM departments    WHERE location = 'New York');

? Notes:

• Subqueries can return:

  • A single value (scalar subquery)

  • A list of values

  • A table/result set

• Can be used in WHERE, FROM, SELECT, HAVING clauses.

• Types:

  • Correlated subquery: references outer query columns, runs row-by-row.

  • Non-correlated subquery: runs independently.

Example of Correlated Subquery:

Find employees whose salary is above the average salary of their department:

SELECT employee_name, salary, department_idFROM employees e1WHERE salary > (    SELECT AVG(salary)    FROM employees e2    WHERE e2.department_id = e1.department_id);

Want me to explain specific types of subqueries or write one for your use case?