Getting started with correlated subqueries
Correlated subqueries are subqueries that reference one or more columns in the main query. Correlated subqueries depend on information in the main query to run, and thus, cannot be executed on their own.
Correlated subqueries are evaluated in SQL once per row of data retrieved—a process that takes a lot more computing power and time than a simple subquery.
In this exercise, you will practice using correlated subqueries to examine matches with scores that are extreme outliers for each country—above 3 times the average score!
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Start the subquery in
WHERE
by taking the average/mean of the home and away goals. - Complete the subquery column references, so that
country_id
is matched in themain
andsub
tables.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
main.country_id,
main.date,
main.home_goal,
main.away_goal
FROM match AS main
WHERE
-- Filter the main query by the subquery
(home_goal + away_goal) >
(SELECT AVG((___ + sub.away_goal) * 3)
FROM match AS sub
-- Join the main query to the subquery in WHERE
WHERE main.___ = sub.___);