Register now or log in to join your professional community.
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.
Read more: http://javarevisited.blogspot.com/2012/07/subquery-example-in-sql-correlated-vs.html#ixzz2m3sqAb57
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 );
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
