Database Day 08: Subquery

1. Simple subquery

Simple subquery doesn't use values from the outer query and is being calculated only once [1]:

SELECT StuID, StuFName, StuLName
FROM Student
WHERE StuID IN
(SELECT StuID
FROM Unit
WHERE UnitName= 'Database');

2. Derived Table/Inline-View subquery


3. Correlated subquery

"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 the outer query." [1]

e.g.  Retrieve one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables [2].

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) ;

or

e.g. Find the Nth max salary by Co-related Sub-query [1].

SELECT Salary
FROM Employee E1
WHERE N-1 = (SELECT COUNT(*)
             FROM Employee E2
             WHERE E1.salary <E2.Salary)

References
[1] http://stackoverflow.com/questions/17268848/difference-between-subquery-and-correlated-subquery
[2] https://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx