← QCE Digital Solutions Unit 2

Sub-query in a WHERE clause with IN

A sub-query is a SELECT inside brackets in a WHERE clause. It returns a list of values, and IN keeps only the rows whose value is in that list. NOT IN keeps the rows that are not in it.

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

Understanding Sub-query in a WHERE clause with IN in QCE Digital Solutions Unit 2

What it does: The inner SELECT sits in brackets inside the WHERE clause. It runs first and gives IN a list of values to test against. This finds students with a mark above 80 without joining the tables. A join could also do it, but the sub-query only returns the students columns, and no columns from results. IN keeps only the rows whose value appears in the list. BETWEEN tests a range, not a list. The sub-query must return one column that matches the type of the field before IN, so use SELECT studentId.

Common mistakes: The FROM keyword is missing. The outer query must name its table with FROM, so it should be SELECT firstName FROM students. The sub-query returns a list of productId values. The = operator only compares to one value, so it should be WHERE productId IN (.

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 Sub-query in a WHERE clause with IN to a class →

This module's key terms are also in a printable crossword (Modules 305–336) 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
Which WHERE clause uses a sub-query to find students who have at least one result?
WHERE studentId IN SELECT studentId FROM resultsWHERE studentId IN (SELECT studentId FROM results)
Matches
SELECT firstName FROM students WHERE studentId IN SELECT studentId FROM results;
↓
The sub-query is missing its brackets. It must be written as IN (SELECT studentId FROM results).
Applying
Meanings
Term
IN (SELECT studentId FROM results)
Meaning
A sub-query is a SELECT inside brackets in a WHERE clause. It returns a list of values, and IN keeps only the rows whose value is in that list. NOT IN keeps the rows that are not in it.
Practice
SELECT firstName, lastName
Testing
Gaps
WHERE studentId
Check Answer
Spots
1SELECT firstName students
2WHERE studentId IN (

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

The 'Hedgehog' badge — First Steps, 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. Which WHERE clause uses a sub-query to find students who have at least one result?
A. WHERE studentId IN (SELECT studentId FROM results) ✓
B. WHERE studentId IN SELECT studentId FROM results
A sub-query is a SELECT inside brackets. Without the brackets, the query has a syntax error.
2. What does the sub-query (SELECT studentId FROM results) return to the outer query?
A. A list of studentId values ✓
B. A full copy of the students table
The sub-query returns a list of values. IN then tests each row of the outer query against that list.
3. Which keyword tests whether a value is found in the list returned by a sub-query?
A. BETWEEN
B. IN ✓
IN keeps only the rows whose value appears in the list. BETWEEN tests a range, not a list.
4. You want students who have NO rows in the results table. Which is correct?
A. WHERE studentId NOT IN (SELECT studentId FROM results) ✓
B. WHERE studentId IN (SELECT studentId FROM results)
NOT IN keeps the rows whose value is not in the list. Plain IN would keep the students who do have results.
5. Which sub-query is valid inside IN to find products that have been ordered?
A. WHERE productId IN (SELECT productId, quantity FROM orders)
B. WHERE productId IN (SELECT productId FROM orders) ✓
For IN, the sub-query must return one column so it gives a single list of values. Selecting two columns causes an error.

Match the Pairs

WHERE studentId IN (SELECT studentId FROM results) → Keeps only the students whose studentId appears in the results table
WHERE studentId NOT IN (SELECT studentId FROM orders) → Keeps only the students who have never placed an order
A sub-query is a SELECT written inside brackets within the WHERE clause of another query → SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM orders);
A sub-query can do the same job as a join: finding students linked to rows in another table → SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results WHERE mark > 80);
SELECT firstName FROM students WHERE studentId IN SELECT studentId FROM results; → The sub-query is missing its brackets. It must be written as IN (SELECT studentId FROM results).
SELECT firstName FROM students WHERE studentId IN (SELECT firstName FROM results); → The sub-query returns the wrong column. IN compares studentId values, so the sub-query must select studentId, not firstName (which is not even in results).

Key Term

IN (SELECT studentId FROM results)
A sub-query is a SELECT inside brackets in a WHERE clause. It returns a list of values, and IN keeps only the rows whose value is in that list. NOT IN keeps the rows that are not in it.

Fill in the Blanks

1. SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results);
Hint: Type the keyword that keeps rows whose value appears in the sub-query's list.
2. SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results);
Hint: Type the keyword that starts the query inside the brackets.
3. SELECT name FROM products WHERE productId NOT IN (SELECT productId FROM orders);
Hint: Type the keyword placed before IN to keep only rows that are NOT in the list.
4. SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results);
Hint: Type the column the inner query returns. It must match the column tested by IN.
5. SELECT name FROM subjects WHERE subjectId IN (SELECT subjectId FROM results);
Hint: Type the table that holds subjectId values for subjects that have marks recorded.

Spot the Error

Question 1
A SELECT firstName students
B WHERE studentId IN (
C SELECT studentId
D FROM results
E );
Line A — The FROM keyword is missing. The outer query must name its table with FROM, so it should be SELECT firstName FROM students.
Question 2
A SELECT name FROM products
B WHERE productId = (
C SELECT productId
D FROM orders
E );
Line B — The sub-query returns a list of productId values. The = operator only compares to one value, so it should be WHERE productId IN (.
Question 3
A SELECT firstName
B FROM students
C WHERE studentId NOT (
D SELECT studentId
E FROM orders);
Line C — NOT must be followed by IN to test against the list from the sub-query. It should be WHERE studentId NOT IN (.
Question 4
A SELECT firstName, house
B FROM students
C WHERE firstName IN (
D SELECT studentId
E FROM results
F WHERE mark > 90);
Line C — The sub-query returns studentId values, so the outer column must be studentId too. Comparing firstName with a list of IDs is wrong. It should be WHERE studentId IN (.
Question 5
A SELECT name FROM product
B WHERE productId IN (
C SELECT productId
D FROM orders
E WHERE quantity > 2);
Line A — There is no table called product. The table is products, so it should be SELECT name FROM products.

Student with a class code? Join your class →