Subquery

A subquery (also known as an inner query or nested query) in SQL is a query embedded within another query. It is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved or to perform an operation in combination with the main query.
Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses. The result of a subquery can be a single value, a single row, multiple rows, or an entire result set.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

In this example, the subquery is `(SELECT column_name FROM another_table WHERE condition)`. It retrieves data from `another_table` based on a specified condition, and the result is used in combination with the main query.

Types of Subqueries

1. Scalar Subquery
   - A subquery that returns a single value and is used within the SELECT, WHERE, or HAVING clause.
   - Example:
     SELECT column1, (SELECT MAX(column2) FROM another_table) AS max_value
     FROM your_table;

2. Row Subquery
   - A subquery that returns a single row and is used within the WHERE clause.
   - Example:
     SELECT column1, column2
     FROM your_table
     WHERE (column1, column2) = (SELECT column1, column2 FROM another_table WHERE condition);

3. Table Subquery
   - A subquery that returns multiple rows and is used within the FROM clause.
   - Example:
     SELECT column1, column2
     FROM (SELECT column1, column2 FROM another_table WHERE condition) AS subquery_result;

4. Correlated Subquery
   - A subquery that references columns from the outer query, allowing data from the outer query to be used in the subquery.
   - Example:
     SELECT column1, column2
     FROM your_table t1
     WHERE column2 = (SELECT MAX(column2) FROM your_table t2 WHERE t1.column1 = t2.column1);

Subqueries provide a powerful way to combine and manipulate data in SQL, allowing for more complex and dynamic queries. They can be used for filtering, comparison, calculation, and other operations within the context of a larger query.