03.4 Simple subqueries

01 | SOME QUESTIONS HAVE TWO PARTS

“Which clubs have above-average capacity?” needs an average before we can judge each club. A subquery is a query nested inside another SQL statement.

We will start with independent, non-correlated subqueries: the inner query does not refer to a row of the outer query. Reading it first is a useful way to understand the result, not a promise about the engine’s execution strategy.

Use this section’s three-table practice database. It is a fresh dataset, separate from Sections 01 and 02. All examples use SQLite. A membership score is an optional fictional activity result from 0 to 100; NULL means not yet recorded, not zero.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
PRAGMA foreign_keys = ON;

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    year_level INTEGER NOT NULL
);
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    fee_cents INTEGER NOT NULL
);
CREATE TABLE Memberships (
    student_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    score INTEGER,
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id)
);

INSERT INTO Students VALUES
    (101, 'Maya', 10), (102, 'Leo', 11), (103, 'Amina', 10),
    (104, 'Noah', 12), (105, 'Maya', 11);
INSERT INTO Clubs VALUES
    (1, 'Robotics', 'Technology', 12, 1500),
    (2, 'Game Design', 'Technology', 16, 0),
    (3, 'Art', 'Creative', 12, 800),
    (4, 'Film', 'Creative', 20, 1000),
    (5, 'Chess', 'Strategy', 16, 0);
INSERT INTO Memberships VALUES
    (101, 1, 'member', 95), (101, 2, 'leader', 85),
    (102, 1, 'member', NULL), (103, 3, 'member', 70),
    (103, 4, 'leader', 90), (104, 2, 'member', 80);
"""
query = """
SELECT club_id, club_name FROM Clubs ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | REFERENCE DATA

03 | FIND ONE COMPARISON VALUE

SELECT AVG(capacity) AS average_capacity
FROM Clubs;

The inner question returns one value, 15.2. We can use it as a scalar, meaning a single value, in an outer condition.

SELECT club_name, capacity
FROM Clubs
WHERE capacity > (SELECT AVG(capacity) FROM Clubs)
ORDER BY capacity DESC, club_id;

Film, Game Design and Chess exceed 15.2. The stored capacity values have not changed; the average supplies a comparison threshold.

04 | THE PARENTHESES KEEP THE SUBQUERY TOGETHER

Read the syntax in layers: SELECT AVG(capacity) FROM Clubs supplies the threshold; the outer WHERE compares each capacity with it; the outer SELECT chooses the displayed columns.

Use one semicolon after the whole statement, not inside the subquery. Put a longer subquery on separate indented lines to make the levels visible.

SELECT club_name, capacity
FROM Clubs
WHERE capacity > (
    SELECT AVG(capacity)
    FROM Clubs
)
ORDER BY club_id;

05 | RETRIEVE ALL MAXIMUM MATCHES

SELECT club_id, club_name, capacity
FROM Clubs
WHERE capacity = (SELECT MAX(capacity) FROM Clubs)
ORDER BY club_id;

MAX finds the value; the outer query retrieves complete rows that match it. If several clubs share the maximum, this pattern returns all of them.

ORDER BY capacity DESC LIMIT 1 would instead choose a single row under its tie rules. Choose the pattern that fits whether the task requires every tied maximum.

06 | A SUBQUERY CAN RETURN A LIST

SELECT student_id, first_name
FROM Students
WHERE student_id IN (
    SELECT student_id
    FROM Memberships
    WHERE role = 'leader'
)
ORDER BY student_id;

The inner query returns leader IDs. IN checks whether each outer student ID belongs to that result. The outer query returns each qualifying student row once, even if their ID appeared several times in the inner list.

Keep the subquery to one selected column for this simple IN form. Returning both student_id and club_id would not fit a single-column membership comparison.

07 | DO NOT USE EQUALS FOR AN ARBITRARY LIST

A scalar subquery must be designed to yield the single value you intend. Use an aggregate such as AVG or a uniquely identified record where appropriate.

If a scalar subquery returns multiple rows, SQLite uses its first row; other databases may reject it. That SQLite behaviour is not a safe way to choose a member from a list, especially without deterministic ordering.

Use IN for the leader-ID list above. If a scalar subquery returns no rows, its value is NULL, so an ordinary equality comparison will not become true.

08 | FIND NON-PARTICIPANTS WITH A SAFE KEY LIST

SELECT student_id, first_name
FROM Students
WHERE student_id NOT IN (
    SELECT student_id
    FROM Memberships
)
ORDER BY student_id;

This works here because Memberships.student_id is required and cannot be NULL. Student 105 is absent from that list.

NOT IN becomes dangerous when its subquery can contain NULL: non-matches can evaluate as unknown and disappear. Inspect the column’s rules, explicitly exclude NULL if appropriate, or use a well-designed alternative such as the LEFT JOIN missing-key pattern.

09 | JOIN OR SUBQUERY?

SELECT club_id, club_name
FROM Clubs
WHERE club_id IN (
    SELECT club_id
    FROM Memberships
    WHERE student_id = 101
)
ORDER BY club_id;

This asks which clubs are in student 101’s membership list. A join could express the same question. Subqueries are useful for membership or threshold tests; joins are often convenient when displaying columns from several related sources.

Neither form is always faster. Choose clear logic, verify the result and investigate performance with actual data if it matters.

10 | GROUP INSIDE A SUBQUERY

SELECT club_id, club_name
FROM Clubs
WHERE club_id IN (
    SELECT club_id
    FROM Memberships
    GROUP BY club_id
    HAVING COUNT(*) >= 2
)
ORDER BY club_id;

The inner query identifies club IDs with at least two memberships. The outer query retrieves their readable names. This combines grouping with a list comparison without adding a join to the outer query.

Build and run the inner query alone first when debugging. Confirm both its values and whether it returns one value or many.

11 | INDEPENDENT PRACTICE

SELECT club_name, fee_cents
FROM Clubs
WHERE fee_cents = (SELECT MAX(fee_cents) FROM Clubs)
ORDER BY club_id;

SELECT student_id, first_name
FROM Students
WHERE student_id IN (
    SELECT student_id FROM Memberships WHERE score >= 90
)
ORDER BY student_id;

KEEP LEARNING

Previous page 03 Combining and summarising data: challenges

References: SQLite joins and grouping · Aggregate functions · Subquery expressions