SUB QUERIES in SQL/Oracle

  • A subquery is a query within a query for the same or different table.
  • The inner query is enclosed in parentheses ().
  • The result of the inner query would be given as input to the outer query.
  • Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time.
    Sub-queries can be used in SELECT, FROM, WHERE and HAVING clauses.

There are four broad divisions of subqueries: –

Single-row subqueries:

Subquery which returns single row output. They mark the usage of single row comparison operators when used in WHERE conditions.

Multiple-row subqueries:

Subquery returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.

Multiple-column subqueries:

Subquery returning multiple column output. They make use of multiple columns and/or multiple rows comparison operators like IN, ANY, ALL.

Correlated subqueries:

Correlated subqueries depend on data provided by the outer query. This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.

Leave a Reply

Your email address will not be published. Required fields are marked *