SQL Subqueries

SQL Subqueries are queries nested within another query. They are used to retrieve data that will be used in the main query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Types of SQL Subqueries:

Single-row Subquery

A subquery that returns only one row and one column. It can be used with operators like =, >, <, etc. Example:

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary) FROM employees
);

Multiple-row Subquery

A subquery that returns multiple rows but only one column. It can be used with operators like IN, ANY, ALL, etc. Example:

SELECT *
FROM employees
WHERE department IN (
    SELECT department FROM departments
    WHERE location = 'New York'
);

Multiple-column Subquery

A subquery that returns multiple rows and columns. It can be used to compare multiple columns. Example:

SELECT *
FROM employees
WHERE (department, salary) IN (
    SELECT department, MAX(salary) FROM employees 
    GROUP BY department
);

Correlated Subquery

A subquery that depends on the outer query. It is executed for each row processed by the outer query. Example:

SELECT *
FROM employees AS e
WHERE salary > (
    SELECT AVG(salary) FROM employees 
    WHERE department = e.department
);