Types of Subquery in SQL
A subquery is a query made within another statement to produce a set of values that are used by the rest of the outer statement.
Types of Subquery:
There are four types of subqueries. These are:
i. Single-row subquery
ii. Multiple row subquery
iii. Single column subquery
iv. Multiple column subquery
A single row subquery is one that returns only a single row in its result. It has the following syntax:
SELECT COLUMN-NAME FROM TABLE-NAME WHERE condition;
Multiple row subquery:
A multiple-row subquery is one that can return any number of rows. When using such a subquery you need to use the special operator IN in the main outer query. The operator IN defines a set of values, one of which must match in the WHERE clause. Therefore, when you use IN in a subquery, SQL simply builds this set from the subquery’s output.
Single column subquery:
A single column subquery is one that returns only one column in its result.
Multiple column subquery:
A multiple-column subquery is one that returns more than one column in its result. The EXISTS operator is used in such queries.