Correlated Subqueries I

Nice work! We can also write correlated subqueries in SQL.

In a correlated subquery, the subquery can not be run independently of the outer query. The order of operations is important in a correlated subquery:

  1. A row is processed in the outer query.
  2. Then, for that particular row in the outer query, the subquery is executed.

This means that for each row processed by the outer query, the subquery will also be processed for that row. In this example, we will find the list of all flights whose distance is above average for their carrier.

SELECT id FROM flights AS f WHERE distance > ( SELECT AVG(distance) FROM flights WHERE carrier = f.carrier);

In the above query the inner query has to be re-executed for each flight. Correlated subqueries may appear elsewhere besides the WHERE clause, they can also appear in the SELECT.

Community Forums
Get help and ask questions in the Codecademy Forums
Report a Bug
If you see a bug or any other issue with this page, please report it here.