Subqueries, sometimes referred to as inner queries or nested queries, are queries that are embedded within the clause of another SQL query. There are different types of SQL subqueries that are frequently used including Scalar, Row, Column, and Table subqueries.
Scalar Subqueries
A scalar subquery is a query that returns exactly one column with a single value.
Example:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name operator
(SELECT column_name [, column_name ]
FROM table_name
WHERE condition);
Row Subqueries
Row subqueries are used to return one or more rows to the outer SQL select query.
Example:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE (column_name [, column_name ])
IN (SELECT column_name [, column_name ]
FROM table_name
WHERE condition);
Column Subqueries
Column Subqueries are used to return one or more columns to the outer SQL select query.
Example:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE (SELECT column_name [, column_name ]
FROM table_name
WHERE condition);
Table Subqueries
Table subqueries are used in the FROM clause and return a table that can be used as a table-reference in an SQL statement.
Example:
SELECT column_name [, column_name ]
FROM
(SELECT column_name [, column_name ]
FROM table1 [, table2 ])
WHERE condition;