Introduction to SQL Using Python: Independent Subqueries

This tutorial will be a brief introduction to INDEPENDENT SUBQUERIES. A SUBQUERY is a query inside a query. An INDEPENDENT SUBQUERY is a subquery that can be run on its own, without the main subquery.

We will be using the Football Delphi database, which can be downloaded here. If you are not familiar with the database, now is a good time to review the different tables and columns in each table. If you have not seen my previous SQL tutorials, the links are below:

• Introduction to SQL Using Python: Filtering Data with the WHERE Statement
• Introduction to SQL Using Python: Computing Statistics & Aggregating Data
• Introduction to SQL Using Python: Using JOIN Statements to Merge Multiple Tables

The following topics will be discussed in this tutorial:

• SUBQUERIES
• SUBQUERIES with Conditional Operators
• SUBQUERIES with Aggregation Functions
• SUBQUERIES that Return More than One Value

To begin, we will download the necessary libraries, sqlite3 and pandas.

Import necessary libraries

Next, you will need to connect to the database and create a cursor object.

Connect to the database and create a cursor object

The following is the format we will be using to run our SQL queries in Python.

Format for SQL queries in Python

SUBQUERIES

A SUBQUERY is a query inside a query. A SUBQUERY is written inside parentheses and will execute before the main query. For example, what if you wanted to find all the teams that had the same number of players as Wolfsburg did during the 2016 season? To do this you would first need to write a query that returned the number of players (KaderHome) on Wolfsburg during the 2016 season. This would be the SUBQUERY. You then could write the main query that compares the KaderHome value in every row of the Teams table to the value returned by the SUBQUERY. To see what this query would look like, examine the query below:

SELECT * FROM Teams WHERE KaderHome = (SELECT KaderHome FROM Teams WHERE TeamName =’Wolfsburg’ AND Season = 2016);

When we run the SUBQUERY on its own below we see that the value returned is 43.

SELECT KaderHome FROM Teams WHERE TeamName =’Wolfsburg’ AND Season = 2016;

Instead of writing 43 in the WHERE statement we wrote the SUBQUERY to compute the number of players on Wolfsburg during the 2016 Season. The outer query compares every KaderHome value to the value returned by the SUBQUERY, 43, and as a result we see the following rows returned and all have 43 as a a KaderHome value.

Practice using SUBQUERIES by writing a query that returns all the Matches from the Matches table, where the number of home goals scored (FTHG) is the same as the number of home goals scored by Bayern Munich on 2010–04–17. Compare your query to the one below:

SELECT * FROM Matches WHERE FTHG = (SELECT FTHG FROM Matches WHERE HomeTeam =’Bayern Munich’ AND Date = ‘2010–04–17’);

SUBQUERIES With Conditional Operators

SUBQUERIES can also be used with other operators besides the equality sign (=). The following operators can also be used with SUBQUERIES:

• > (Greater than)
• >= (Greater than or equal to)
• < (Less than)
• <= (Less than or equal to)
• != (Not equal to)

The below query will return data for any team with a higher StadiumCapacity value than Stuttgartt’s StadiumCappacity during the 2010 Season:

SELECT * FROM Teams WHERE Season = 2010 AND StadiumCapacity > (SELECT StadiumCapacity FROM Teams WHERE TeamName =’Stuttgart’ AND Season = 2010);

Practice using comparison operators with SUBQUERIES by writing a query that returns all the rows from the Teams table where the average age of the players (AvgAgeHome) is less than the average of team players on the Dortmund team during the 2012 Season. Compare your query with the one below:

SELECT * FROM Teams WHERE AvgAgeHome < (SELECT AvgAgeHome FROM Teams WHERE TeamName =’Dortmund’ AND Season = 2012) ORDER BY AvgMarketValueHome DESC;

SUBQUERIES with Aggregation Functions

We can also use the aggregation functions discussed in my previous blogs within SUBQUERIES. The below query will show all the teams from the 2014 Season where the number of foreign players is higher than the average number of foreign players on all teams during the 2014 season.

SELECT * FROM Teams WHERE Season = 2014 AND ForeignPlayersHome > (SELECT AVG(ForeignPlayersHome) FROM Teams WHERE Season = 2014);

To practice aggregating with SUBQUERIES write a query that will show each team from the Teams table where the sum of that teams OverallMarketValueHome is less than the sum of Bayern Munich OverallMarketValue. Compare your query to the one below:

SELECT * FROM Teams GROUP BY TeamName HAVING SUM(OverallMarketValueHome) < (SELECT SUM(OverallMarketValueHome) FROM Teams WHERE TeamName = ‘Bayern Munich’);

SUBQUERIES That Return More than One Value

The previous queries all included SUBQUERIES that returned a single value. SUBQUERIES can also return more than a single value. Look at the query below to see how this is done.

SELECT * FROM Teams WHERE Season = 2014 AND KaderHome IN (36, 38, 40);

Every team that had 36 players, 38 players or 40 players was returned.

In the below query, the subquery returns all the HomeTeams from the Matches table that begin with a “D”. The outer query compares every TeamName from 2013 in the Teams data table. If a TeamName in the outer query matches any of the values/HomeTeams in the inner SUBQUERY, that TeamName will be returned.

SELECT * FROM Teams WHERE Season = 2013 AND TeamName IN (SELECT HomeTeam FROM Matches WHERE HomeTeam LIKE ‘D%’);

You have reached the end of this tutorial. We have discussed INDEPENDENT SUBQUERIES and the following topics:

• SUBQUERIES
• SUBQUERIES with Conditional Operators
• SUBQUERIES with Aggregation Functions
• SUBQUERIES that Return More than One Value

Keep practicing with this database and using subqueries to make your SQL queries more complex!