What is a Subquery

A subquery, also known as a nested query or inner query, is a query nested within another query. Subqueries are used to retrieve data based on the results of another query or to perform additional filtering, calculations, or comparisons within a larger query. Subqueries can be used in various parts of a SQL statement, such as SELECT, INSERT, UPDATE, DELETE, and WHERE clauses. Here are the main types of subqueries:- 

1. Single-Row Subquery:-  A single-row subquery returns only one row of results, typically used with comparison operators that expect a single value. For example:- 

SELECT column1

FROM table1

WHERE column2 = (SELECT MAX(column2) FROM table1);

This query retrieves the value of column1 from table1 where column2 has the maximum value in the same table.

2. Multiple-Row Subquery:-  A multiple-row subquery returns multiple rows of results, often used with IN or EXISTS operators to match multiple values. For example:- 

SELECT column1

FROM table1

WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

This query retrieves the value of column1 from table1 where column2 matches any value returned by the subquery.

3. Correlated Subquery:-  A correlated subquery references columns from the outer query, allowing for comparisons with values from the outer query. For example:- 

SELECT column1

FROM table1 t1

WHERE column2 > (SELECT AVG(column2) FROM table1 WHERE column3 = t1.column3);

In this query, the subquery calculates the average of column2 for each distinct value of column3 in table1, and the outer query selects rows where column2 is greater than this average value, with the correlation on column3.

4. Nested Subquery:-  A nested subquery contains another subquery within it, allowing for multiple levels of nesting. For example:- 

SELECT column1

FROM table1

WHERE column2 IN (SELECT column2 FROM table2 WHERE column3 IN (SELECT column3 FROM table3));

In this query, the innermost subquery retrieves values from table3, which are then used in the middle subquery to filter rows from table2, and finally, the outer query selects rows from table1 where column2 matches values returned by the middle subquery.

Subqueries are powerful tools in SQL that allow for complex data retrieval and manipulation. They can be used to perform operations that would otherwise be difficult or impossible to express using a single query.