s5.07.06 - advanced sql joins and subqueries

Master advanced SQL JOINs and subqueries. Retrieve complex data from normalised relational databases with precision.

You've mastered querying individual tables and making simple updates. But what if you want to find out which cities I've visited in countries where Arabic is the official language? Normalisation helps keep your data tidy, but to answer complex questions like this, you need advanced tools. Today, you're becoming a Data Analyst and Database Developer, learning how to link multiple tables using

JOIN

statements and nest queries using

Subqueries

to unlock deep insights from your relational database.

Learning Outcomes

Factual (Knowing that)

Recall the definition and purpose of an

INNER JOIN

in SQL.

Describe the basic structure of a subquery (nested

SELECT

statement).

State the role of table aliases in complex SQL queries.

Conceptual (Knowing why)

Analyse why

JOIN

operations are essential for retrieving complete information from a normalised relational database.

Explain how subqueries can be used to filter data based on the results of another query.

Evaluate the benefits of using table aliases for readability and potentially performance in multi-table queries.

Procedural (Knowing how)

Apply the

INNER JOIN

clause to combine data from two or more related tables based on common attributes.

Implement subqueries within

WHERE

FROM

clauses to achieve complex filtering or data sets.

Construct advanced SQL

SELECT

queries to solve real-world data retrieval problems involving multiple tables and nested logic.

The Deep Dive

Advanced SQL: Weaving Data Together with JOINs, Subqueries and Aggregate Functions

You've successfully built and manipulated data in individual tables. But the true power of a relational database (and the challenge for any Data Analyst or Database Developer) comes from working with multiple, linked tables to answer complex questions. This is where JOINs and Subqueries become indispensable tools.

Bringing Tables Together: The INNER JOIN

Remember how normalisation splits data across different tables to prevent redundancy? For example, the

City

table stores

CountryCode

as a Foreign Key to link to the

Country

table. If you want to list city names alongside their full country names, you need a way to combine information from both tables. That's precisely what the

JOIN

clause does.

The most common type is the INNER JOIN. An

INNER JOIN

combines rows from two or more tables where there is a matching value in both tables based on a specified condition (usually the relationship between a Primary Key and a Foreign Key). Only records that have matches in both tables are included in the result.

CRUCIAL: Now that we are dealing with multiple tables with potential identical field names, it's important to specify the table name alongside the field name to avoid the dreaded

ambiguous column name

error - instead of

field

you type

table.field

for example,

Country.CountryName

Basic Syntax:

SELECT TableA.column, TableB.column
FROM TableA
INNER JOIN TableB ON TableA.ForeignKey = TableB.PrimaryKey
WHERE Condition

TableA

and

TableB

: The tables you want to join.

ON TableA.ForeignKey = TableB.PrimaryKey

: This is the crucial condition that tells SQL how the tables are related. You specify the matching columns from each table.

Condition

: The filter to apply to the fields. Remember to qualify the table...

Example: List all cities with their corresponding country names.

Let's imagine our

City

table has

CityName

and

CountryCode

, and our

Country

table has

CountryCode

and

CountryName

SELECT City.CityName, Country.CountryName
FROM City
INNER JOIN Country ON City.CountryCode = Country.CountryCode
WHERE Country.CountryCode = 'GBR'
-- Copy this and try it in the SQLite Editor

Queries Within Queries: The Subquery

A subquery (also known as a nested query or e) is a

SELECT

statement embedded inside another SQL query. It runs first, and its result is then used by the outer query. Subqueries are powerful for filtering data where the filtering condition depends on another query's output.

Basic Syntax (using

WHERE

with

IN

)

SELECT column1
FROM TableName
WHERE columnX IN (SELECT columnY FROM AnotherTable WHERE Condition);

The inner

SELECT

statement (the subquery) executes first, returning a set of values. The outer

SELECT

statement then uses this set of values in its

WHERE

clause.

Example: Find all cities located in the region 'Caribbean'. We don't directly store RegionName in the City table.

1. First, find the

RegionCode

for 'Caribbean' from the

Region

table.
2. Then, find countries in that

RegionCode

from the

Country

table.
3. Finally, find cities in those countries from the

City

table.

SELECT CityName
FROM City
WHERE CountryCode IN (SELECT CountryCode
                      FROM Country
                      WHERE RegionCode = (SELECT RegionCode
                                          FROM Region
                                          WHERE RegionName = 'Caribbean'));
-- Copy this and try it out in the SQLite Editor

Summarising Data: Aggregate Functions and DISTINCT

When analysing large datasets within a relational database, you frequently need to summarise information rather than simply retrieving individual rows. SQL provides Aggregate Functions and the DISTINCT keyword to achieve this, allowing you to generate overarching insights from complex data.

Removing Duplicates with DISTINCT

The DISTINCT keyword is used directly after the

SELECT

command to return only unique values. If your query naturally results in multiple identical rows,

DISTINCT

filters out the duplicates so you only see one instance of each value.

Example: Find a list of all unique country codes where cities are located within the

City

table.

SELECT DISTINCT CountryCode
FROM City;
-- Copy this and try it out in the SQLite Editor

Calculating Insights with Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single summarising value. They are the essential tools for answering questions like "how many?", "what is the total?", or "what is the average?"

The core aggregate functions required for A-Level Computer Science include:

COUNT(): Returns the total number of rows that match a specified criterion.

SUM(): Calculates the total sum of a numeric column.

AVG(): Calculates the average value of a numeric column.

MAX(): Finds the highest value in a column.

MIN(): Finds the lowest value in a column.

Example 1 (COUNT): How many cities are recorded in the

City

table?

SELECT COUNT(CityID) AS TotalCities
FROM City;
-- Copy this and try it out in the SQLite Editor

Notice the

AS

keyword in the above query. This is used to define an 'alias' for the field so, in this case, instead of returning

count(CityID)

as the field name, the query will return

TotalCities

as the field name.

Example 2 (Combining COUNT and DISTINCT): You can nest these concepts. For example, to find out how many unique countries have been visited based on the

Visited

and

City

tables:

SELECT COUNT(DISTINCT CountryCode) AS CountriesVisited
FROM City
INNER JOIN Visited ON City.CityID = Visited.CityID;
-- Copy this and try it out in the SQLite Editor

Grouping Aggregates: GROUP BY

To calculate aggregate functions for specific categories rather than the entire table, you must use the GROUP BY clause. This groups rows that share the same values in specified columns into summary rows.

Example (SUM with GROUP BY): What is the total population for each country, calculated using the

Population

field in the

City

table?

SELECT CountryCode, SUM(Population) AS TotalPopulation
FROM City
GROUP BY CountryCode;
-- Copy this and try it out in the SQLite Editor

Why Master Advanced SQL?

For Software Developers, Database Administrators, and anyone working with data, these advanced querying techniques are critical.

Complex Reporting: Generate detailed reports by combining disparate but related pieces of information.

Data Analysis: Extract meaningful insights from large datasets by applying sophisticated filtering and aggregation.

Application Logic: Build robust applications (like your NEA project!) that can interact with normalised databases efficiently, retrieving exactly the data needed for display or further processing.

Efficiency: Well-constructed

JOIN

s and

Subqueries

can be more efficient than multiple separate queries, reducing load on the database server.

You'll now put these skills to the test with the 'World' database, solving complex challenges that require you to think relationally and strategically about data retrieval.

Active Learning Directive
Force a connection. Identify two concepts from these notes and explicitly link them to a topic we covered in a previous module or term. Write a short paragraph explaining how understanding one strengthens your understanding of the other.

Comprehension Questions

Answer these questions on your lesson sheet.

Define the purpose of an

INNER JOIN

in SQL and explain when it is used.

Describe the basic structure of an SQL subquery and how its output is used by the outer query.

Explain the benefit of using table aliases in an SQL query that involves multiple

JOIN

operations.

How do

INNER JOIN

s help overcome the challenges introduced by database normalisation for data retrieval?

Provide an example scenario where a subquery would be more appropriate than a simple

WHERE

clause for filtering data.

Task World Data Explorer: Deep Dive into Global Data

Welcome, Master Data Explorer! You've successfully built and populated the 'World' database. Now it's time to truly unlock its potential by performing complex multi-table queries and subqueries. Your mission is to navigate the interconnected data, extracting precise insights like a seasoned Data Analyst or Database Developer.

Get Organised!

Open your

world.db

file in The Computing Café SQLite Editor. If you haven't fully populated it from the 'WorldDump' SQL files, ask your teacher for a completed copy.

Keep your printed ER diagram for the 'World' database handy - it's your map to the relationships!

Explore with

INNER JOIN

For each question below, construct an SQL

SELECT

query that uses

INNER JOIN

(or multiple

INNER JOIN

s) to retrieve the required information. Copy your query onto your lesson sheet and note the number of rows returned (given in brackets).

List out the names of all the cities in Pakistan (51 rows).

List out the names of the Regions in Latin America (3 rows).

List out the year visited and city name for all cities that I have visited and given a rating of more than 5 out of 10 in ascending order of year visited (20 rows).

Tell me the name of, and the year I visited, my favourite city in the 'United States' (1 row).

List out the names of all the countries where the official language is Arabic (17 rows).

Tackle Advanced Queries with Aggregates and Subqueries

These challenges require more complex SQL, often involving aggregate functions (

COUNT()

SUM()

), the

DISTINCT

keyword, or nested subqueries. Copy your query onto your lesson sheet and note the number of rows returned.

List out the names of the regions where Spanish is spoken (5 rows).

HINT: You'll need to join at least three tables and use

DISTINCT

How many countries did I visit between 1980 and 1990 (inclusive) where 'English' was spoken (1 row).

HINT: Use the

COUNT()

function,

DISTINCT

, and filter by

YearVisited

and

LanguageName

List out the total populations of Germany, France and Belgium in decreasing order of total population (3 rows).

HINT: Use the

SUM()

function,

AS

for an alias, the

IN

operator, and the

GROUP BY

clause.

Hungry for more?

Search the web: SQL LEFT JOIN RIGHT JOIN FULL OUTER JOIN examples

Ask an AI: Click on the carefully written prompt below to learn more...

Role: Lead Database Administrator. Task: Explain the performance implications and optimisation strategies for complex SQL queries involving multiple JOINs and subqueries to an A-Level Computer Science student. Limit to 180 words. NO intro, NO outro, NO deviation from the topic, NO follow-up questions.

Explore Analytical Functions: Research advanced SQL analytical functions like

ROW_NUMBER()

RANK()

, and

LAG()

and how they can be used for sophisticated data analysis.

Outcome: A successfully queried and modified

world.db

database for all Medium and Hard challenges, demonstrating proficiency in advanced SQL and DML commands.

Sticky Note Challenge

On a sticky note, write down the names of the three tables you had to join for the "Regions where Spanish is spoken" query, and the foreign key/primary key links you used.

Today you have mastered advanced SQL techniques including INNER JOINs, subqueries and aggregate functions to retrieve complex, interconnected data from normalised relational databases.

Last modified: October 19th, 2026