What is sub query? And what are the types of sub query?

A subquery is a SELECT statement within another statement.

• A subquery may occur in :
  • – A SELECT clause
  • – A FROM clause
  • – A WHERE clause
• The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
• A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
• You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
• A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
• The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.

Types of Subquery

• Single row subquery : Returns zero or one row.
• Multiple row subquery : Returns one or more rows.
• Multiple column subqueries : Returns one or more columns.
• Correlated subqueries : Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
• Nested subqueries : Subqueries are placed within another subquery.