Database - Advanced SQL - Discussion

Discussion :: Advanced SQL - General Questions (Q.No.12)

12. 

Which of the following is a correlated subquery?

[A]. Uses the result of an inner query to determine the processing of an outer query.
[B]. Uses the result of an outer query to determine the processing of an inner query.
[C]. Uses the result of an inner query to determine the processing of an inner query.
[D]. Uses the result of an outer query to determine the processing of an outer query.

Answer: Option B

Explanation:

Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.

Sundar said: (Sep 13, 2011)  
A ' correlated sub-query' is a term used for specific types of queries in SQL in computer databases. It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query.

Here is an example for a typical correlated sub-query. In this example we are finding the list of employees (employee number and names) having more salary than the average salary of all employees in that employee's department.

SELECT employee_number, name
FROM employee AS e1
WHERE salary > (SELECT avg(salary)
FROM employee
WHERE department = e1.department);


In the above query the outer query is,

SELECT employee_number, name
FROM employee AS e1
WHERE salary >


And the inner query is,

(SELECT avg(salary)
FROM employee
WHERE department = e1.department);


In the above nested query the inner query has to be executed for every employee as the department will change for every row. Hence the average salary will also change.

The effect of correlated sub-queries can also be obtained using outer Joins.

Source: http://en.wikipedia.org/wiki/Correlated_subquery

Vasu said: (Jul 18, 2015)  
So what is the correct answer for this question.

As per book with below link the answer should be option "B" but you are mentioning the answer is option "A".

This question answer should be Option B.

B -> "Uses the result of an outer query to determine the processing of an inner query".

Kindly confirm the correct answer.

Post your comments here:

Name *:

Email   : (optional)

» Your comments will be displayed only after manual approval.