Types of Subqueries
There are three types of subqueries, differentiated by how many rows and columns they return.
1. Scalar subqurey
2. Row subquery
3. Table subquery
Scalar subquery :
If a subquery returns exactly one column and one row, it is known as a scalar subquery.
Row subquery :
If a subquery returns multiple columns and exactly one row, it is known as a row subquery. A row subquery is a derivation of a scalar subquery and can thus be used anywhere that a scalar subquery can be used.
Table subquery :
if a subquery can return multiple columns and multiple rows, it is known as a table subquery. A table subquery is legal everywhere that a table reference is legal in an SQL statement, including the FROM clause of a SELECT. It, too, is usually found in a WHERE clause, immediately after an IN or EXISTS predicate or a quantified comparison operator.
