Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

What is the difference between dependent subquery and independent subquery ?

user-image
Question ajoutée par Mohammad Ateieh , Software Engineering Manager , Bayt.com
Date de publication: 2013/11/06
Muhammad Majid Saleem
par Muhammad Majid Saleem , Senior PHP Developer / Project Manager , SwaamTech

Depdent Subquery:

If a subquery uses any refrence / alies from outer query then it would be called as Dependent / Correlated Subquery.

 

Example:

SELECT * FROM Employee Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2WHERE Emp2.Salary > Emp1.Salary);

 

Note: Emp1 is an alies of outer query and it is being used in subquery.

 

Indepdent Subquery:

If a subquery does use any refrence / alies from outer query then it would be called as Independent / Incorrelated Subquery.

 

Example:

SELECT Salesperson.Name from Salesperson WHERE Salesperson.ID NOT IN(SELECT Orders.salesperson_id from Orders, Customer WHERE Orders.cust_id = Customer.ID AND Customer.Name = 'Samsonic');

 

Note: Subquery is not being used any reference from outer query and it can execute independently.

 

In case of correlated subquery inner query depends on outer query while in case of noncorrelated query inner query or subquery doesn't depends on outer query and run by its own. But ,In case of correlated subquery, outer query executed before inner query or subquery while in case of NonCorrelated subquery inner query executes before outer query. Also Correlated Sub-queries are slower than non correlated subquery and should be avoided in favor of sql joins.

Read more: http://javarevisited.blogspot.com/2012/07/subquery-example-in-sql-correlated-vs.html#ixzz2m3sqAb57

Rashid Anwar
par Rashid Anwar , senior php developer , sparx it solution pvt ltd.

it is also called correlated and uncorrelated subquery.

Correlated Subquery:

A subquery can contain a reference to an object defined in a parent statement. This is called an outer reference. A subquery that contains an outer reference is called a correlated subquery. Correlated subqueries cannot be evaluated independently of the outer query because the subquery uses the values of the parent statement. That is, the subquery is performed for each row in the parent statement. Thus, results of the subquery are dependent upon the active row being evaluated in the parent statement.

For example, the subquery in the statement below returns a value dependent upon the active row in the Products table:

SELECT Name, Description FROM Products WHERE Quantity <2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems WHERE Products.ID=SalesOrderItems.ProductID );

Uncorrelated Subquery: A subquery that does not contain references to objects in a parent statement is called an uncorrelated subquery.In the example below, the subquery calculates exactly one value: the average quantity from the SalesOrderItems table.In evaluating the query, the database server computes this value once, and compares each value in the Quantity field of the Products table to it to determine whether to select the corresponding row.

 

SELECT Name, Description FROM Products WHERE Quantity <2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems );

kafui nyamalor
par kafui nyamalor , graphic designer , Gizzo Multimedia

Correlated Subquery is a sub-query that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.

SELECT Salesperson.Name from Salesperson WHERE Salesperson.ID NOT IN(SELECT Orders.salesperson_id from Orders, Customer WHERE Orders.cust_id = Customer.ID AND Customer.Name = 'Samsonic');

 

because the not in queries are set operators it used to find two defernt values from two different tables

 

More Questions Like This