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

View Course

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 the main and sub 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.___);

Edit and Run Code