← QCE Digital Solutions Unit 2

Running a join query from code

Runs a JOIN query stored in one string. Each row fetched back is a tuple holding fields from both tables, which you can unpack into variables.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 77

Understanding Running a join query from code in QCE Digital Solutions Unit 2

What it does: Triple quotes let the single SQL string run over several lines, so the JOIN is easier to read. fetchall() returns the joined rows as a list of tuples, with fields from both tables in each tuple. Each row is a tuple, so you can unpack it straight into variables in the for line. The number of variables must match the number of selected columns. The database does the joining. A single execute call sends one query and the results come back already combined.

Common mistakes: The alias r is never declared, so r.mark and r.studentId are unknown. It should be FROM students s JOIN results r. A query written across several lines needs triple quotes. A single " cannot span lines. It should be join_sql = """.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Running a join query from code to a class →

This module's key terms are also in a printable crossword (Modules 337–409) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A JOIN query is run from Python code. How is the SQL passed to cur.execute()?
As separate strings, one for each table being joinedAs one string, which can be split over several lines with triple quotes
Matches
join_sql = "SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId"
↓
A single-quoted string with double quotes cannot span several lines, so Python raises a syntax error. Use triple quotes for a multi-line SQL string.
Applying
Meanings
Term
cur.execute(join_sql)
Meaning
Runs a JOIN query stored in one string. Each row fetched back is a tuple holding fields from both tables, which you can unpack into variables.
Practice
import sqlite3
Testing
Gaps
cur.(sql)
Check Answer
Spots
2SELECT s.firstName, r.mark
3FROM students s JOIN results

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Butterfly' badge — Branch Hopper, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A JOIN query is run from Python code. How is the SQL passed to cur.execute()?
A. As separate strings, one for each table being joined
B. As one string, which can be split over several lines with triple quotes ✓
The whole JOIN statement is a single string. Triple quotes let you lay it out over several lines so it is easier to read.
2. After cur.execute() runs a join of students and results, what does each row from fetchall() hold?
A. A tuple with fields from both tables, such as ('Ava', 88) ✓
B. Two separate tuples, one from each table
A join produces one combined row. Each fetched tuple holds the selected fields from both tables side by side.
3. Which line correctly unpacks a joined row that has a first name and a mark?
A. for first_name, mark in rows: ✓
B. for first_name in rows, mark in rows:
Each row is a tuple, so you can unpack it straight into variables in the for line. The number of variables must match the number of selected columns.
4. The query is: SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId. How many values are in each fetched tuple?
A. 2, one for each column in the SELECT ✓
B. 4, one for each column in the two tables
The tuple only holds the columns named in SELECT, not every column from both tables.
5. Which is the better way to store a long JOIN query in Python?
A. join_sql = "SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId"
B. join_sql = """SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId""" ✓
Ordinary double quotes cannot span several lines, so that version causes a syntax error. Triple quotes allow a multi-line string.

Match the Pairs

cur.execute(join_sql) → Runs a JOIN query held in one string variable and gets the joined rows ready to fetch
for first, subject, mark in rows: → Unpacks each joined row tuple into variables, with fields that came from different tables
A long JOIN query can be stored as one readable string by using a multi-line string → join_sql = """SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId"""
Fetching all rows of a join gives a list of tuples, each holding one student field and one subject field → rows = cur.fetchall()
join_sql = "SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId" → A single-quoted string with double quotes cannot span several lines, so Python raises a syntax error. Use triple quotes for a multi-line SQL string.
for first, subject in rows: # rows have 3 fields → The loop unpacks two variables, but each joined row tuple holds three fields, so Python raises a ValueError. Unpack one variable per selected field.

Key Term

cur.execute(join_sql)
Runs a JOIN query stored in one string. Each row fetched back is a tuple holding fields from both tables, which you can unpack into variables.

Fill in the Blanks

1. cur.execute(sql) rows = cur.fetchall()
Hint: Type the cursor method that sends the SQL string to the database.
2. sql = """SELECT firstName, mark FROM students JOIN results ON students.studentId = results.studentId"""
Hint: Type the keyword that combines the two tables into one result.
3. cur.execute(sql) for row in cur.fetchall(): print(row)
Hint: Type the cursor method that returns every joined row as a list of tuples.
4. row = cur.fetchone() name, mark = row print(name, "got", mark)
Hint: Type the variable holding the joined tuple that is being unpacked.
5. sql = """SELECT name, quantity FROM products JOIN orders ON products.productId = orders.productId"""
Hint: Type the shared field in the orders table that links back to the products table.

Spot the Error

Question 1
A sql = """
B SELECT s.firstName, r.mark
C FROM students s JOIN results
D ON s.studentId = r.studentId
E """
F cur.execute(sql)
Line C — The alias r is never declared, so r.mark and r.studentId are unknown. It should be FROM students s JOIN results r.
Question 2
A join_sql = "
B SELECT p.name, o.quantity
C FROM orders o JOIN products p
D ON o.productId = p.productId
E """
F cur.execute(join_sql)
Line A — A query written across several lines needs triple quotes. A single " cannot span lines. It should be join_sql = """.
Question 3
A cur.execute(join_sql)
B rows = cur.fetchall()
C for name, mark, grade in rows:
D print(name, "got", mark)
Line C — Each joined row is a tuple with two fields, so it cannot be unpacked into three variables. It should be for name, mark in rows:.
Question 4
A cur.execute(join_sql)
B rows = cur.fetchall()
C for row in rows:
D name, mark = row[0]
E print(name, mark)
Line D — row[0] is only the first field, a single value, so it cannot be unpacked into two variables. Unpack the whole tuple: name, mark = row.
Question 5
A cur = conn.cursor()
B cur.execute("join_sql")
C rows = cur.fetchall()
D print(rows[0])
Line B — Quotes turn the variable name into the text join_sql, which is not valid SQL. Pass the variable that holds the query: cur.execute(join_sql).

Student with a class code? Join your class →