Subquery

A subquery is query within a query. These subqueries ca reside in the WHERE clause, the FROM clause, or the SELECT clause. A subquery is a query that is nested inside a SELECT, UPDATE or DELETE statement, or inside another subquery. 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. A subquery can also be found in the FROM clause. These are called inline views. Oracle allows up to 255 level of subqueries in the WHERE clause. can be aliased. Oracle allows an unlimited number of subqueries in the FROM clause. Scalar subqueries - allows us to place individual queries inside the SELECT clause.

• The Subquery (inner query) executes once before the main query
• The result of the subquery is used by the main query (outer query)
• Place subquery on the right side of the comparison operator.
• Do not add an order by clause to a subquery
• use single row operators with single row subqueries
• use multiple row operators with multiple row subqueries

Single row operators :- =, >, >= ...etc

Multiple row operators :-

1. IN - Equal to any number in the list
2. ANY - Compare value to each value returned by the subquery
3. ALL - Compare value to every value returned by the subquery