Tutorials Hut

  • SQL Testing




  • Independent Subquery and
    Correlated Subquery

    In Independent subquery ,Inner query is independent of outer query.

    This article will present you with a complete idea about Independent sub query and Correlated sub query.

    We will learn below topics in this article

    Independent Subquery

    Inner query is executed first and the results are stored then Outer query then runs on the stored results.

    If a subquery does use any refrences from outer query then it would be called as Independent  Subquery.

    it is also called Incorrelated sub query.

    Example:

    Consider Employee table having below records.

    Employee_Table1
    Find minimum salary of the Employee in a company
    SELECT Employee_FirstName, Employee_LastName,Salary
    FROM Employee
    WHERE salary = (SELECT MIN (salary) FROM Employee)
    SQL Independent subquery

    Correlated Subquery

    • Inner query depends on outer query
    • Outer query needs to be executed before the inner query.
    • The inner query is executed separately for each row of the outer query.
    • These queries are slower compare to independent sun query
    • Data from each row in the main query is passed to the subquery for processing
    Syntax
    SELECT ColumnNames from Table1  t1 WHERE column1  
    [Comparison Operator]
     (SELECT ColumnNames
         FROM Table2 WHERE exp1 = t1.exp2);

    Example:

    Consider Employee table having below records.

    Employee_Table1

    Find all the employees who have more than average salary in their department.

    SELECT Employee_FirstName,Employee_LastName, Salary, Department_id
    FROM Employee e1
    WHERE salary > (SELECT AVG(salary)
    FROM Employee
    WHERE DepartmentID = e1.DepartmentID);



  • SQL Testing


















  • Leave a Reply

    Your email address will not be published. Required fields are marked *