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: