What is a Sub Query?
A query nested inside another query and it is used when a query is based on an unknown value. A sub query is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query the sub query must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum.
What are the types of Sub Queries?
Single Row Sub Query
Return the outer query one row of results that consists of one column
Multiple Row Sub Query
Return to the outer query more then one row of the results it Require use of IN, ANY, ALL, or EXISTS operators
Multiple Column Sub Query
Return to the outer query more then one row of the results in this type of query Column list on the left side of operator must be in parentheses it use the IN operator for WHERE and HAVING clauses
Correlated Sub Query
Reference a column in the outer query and execute the sub query once for every row in outer query
Uncorrelated Sub Query
Execute the sub query first and passes the value to outer query
Source Oracle Introduction to Database