About Correlated Subqueries

Correlated subqueries are subqueries ( sub query is a query nested inside another query) where the inner query has a reference to a column from the table in the outer query. you can’t just highlight the inner portion and run it independently. For example

SELECT productid, productname, unitprice FROM Production.Products WHERE unitprice in (SELECt sale_price from sale_product where Production.Products=Sale_Products.productid ) FROM Production.Products);

Here is another example for a typical correlated subquery. In this example, the objective is to find all employees whose salary is above average for their department.

SELECT employee_number, name
FROM employees AS emp
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = emp.department);

In the above query the outer query is

SELECT employee_number, name
FROM employees AS emp
WHERE salary > …

and the inner query (the correlated subquery) is

SELECT AVG(salary)
FROM employees
WHERE department = emp.department

In the above nested query the inner query has to be re-executed for each employee. (A sufficiently smart implementation may cache the inner query’s result on a department-by-department basis, but even in the best case the inner query must be executed once per department.

Correlated subqueries may appear elsewhere besides the WHERE clause; for example, this query uses a correlated subquery in the SELECT clause to print the entire list of employees alongside the average salary for each employee’s department. Again, because the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.

SELECT
employee_number,
name,
(SELECT AVG(salary)
FROM employees
WHERE department = emp.department) AS department_average
FROM employees AS emp;

Articles in the course

  1. Multi-Join Queries
  2. Subqueries
  3. Correlated Subqueries
  4. Sequence number and Computed columns
  5. Inline Functions
  6. Heaps and Balanced Trees
  7. Implementing Nonclustered Indexes
  8. Implementing Indexed Views
  9. Supporting Queries with Indexes
  10. Search Arguments
  11. Auto-Created Statistics
  12. Manually Maintaining Statistics
  13. The Meaning of “Set-Based”
  14. Iterations for Operations That Must Be Done Per Row
  15. Cursor vs. Set-Based Solutions for Data Manipulation Tasks
  16. Scope
  17. DDL and Indexes
  18. Physical Representation in tempdb
  19. Transactions
  20. Statistics
  21. Full-Text Search Components
  22. Creating and Managing Full-Text Catalogs and Indexes
  23. The CONTAINS Predicate
  24. The FREETEXT Predicate
  25. Using the Full-Text Search Functions
  26. Using the Semantic Search Functions
  27. Introduction to XML
  28. Shredding XML to Tables
  29. XQuery Basics
  30. Navigation
  31. FLWOR Expressions
  32. When to Use the XML Data Type
  33. XML Data Type Methods
  34. Using the XML Data Type for Dynamic Schema
  35. Access Methods
  36. Join Algorithms
  37. Other Plan Iterators
  38. Parameterized Queries
  39. Batch Processing
  40. Optimizer Hints
  41. Plan Guides