top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is a CO-RELATED SUBQUERY?

+1 vote
219 views
What is a CO-RELATED SUBQUERY?
posted Jun 1, 2015 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

Correlated Sub Query – a sub query/inner query which gets executed multiple times for the outer query is called as correlated sub query. In correlated sub query, you take the reference of outer query column in sub query.

    SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

Here is the result set.

LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282
(2 row(s) affected)
answer Jun 1, 2015 by Shivaranjini
0 votes

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.
eg
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y
where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

answer Jun 3, 2015 by Arun Gowda
...