|
Oracle Sub-Queries - August 11, 2008 at 15:00 PM by Amit Satpute
What are the guidelines for using SUB-QUERIES?
Answer
Following things need to be kept in mind when you write
sub-queries:
-
Caution should be taken with simple sub-query, especially when a normal value
operator is used on the results of a sub-query, only one field must be
returned
-
If you want to check for the existence of a single value within a set of other
values, use the IN keyword as an operator upon the result set from a sub-query.
Example of a Sub-Query:
SELECT name FROM students
WHERE stud_id = (SELECT stud_id FROM class
WHERE last_name='abc'
AND first_name='xyz');
What is correlated sub-query?
Answer
In a simple SubQuery, the result retrieved by the inner query is fed
to the outer query. The outer query takes the result as its input and processes
it to produce its output.
However, in a corelated sub query, a correlated sub-query is dependent upon the
outer query.
The outer query and the sub-query are related typically through a WHERE
statement located in the sub-query.
The sub query gives a reference to the outer query. Then the outer query
executes and the result is returned to the sub query. Finally the sub query is
executed for every row that is selected by the outer query.
|