Table transformation: correlated subqueries

Date: April 17th 2016
Last updated: April 17th 2016

A correlated subquery is executed row-wise. That is the outer subquery is run each time for each row which in turn executes the inner subquery and then moves to the next row, and so on.

In my example each person (name) has the subquery executed to see if they are below the average distance of all people (names). Only the names are returned.

 /* find each name that has an above avg distance */
SELECT name
  FROM myTable AS mt
  WHERE distance < (
    SELECT AVG(distance)
    FROM myTable
    WHERE name = mt.name);

This example was originally seen in codeacademy's SQL tutorial:

• https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-i?utm_source=customerio&utm_campaign=sql_table_transformation_announcement&utm_medium=email_newsletter&utm_content=cta