← ACARA Digital Technologies Years 9–10

BETWEEN and IN in a WHERE clause

Tests if a value is inside a range, including both limits. Its partner IN (a, b, c) checks a value against a list, replacing repeated OR. NOT IN excludes the list.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 32, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 65, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 31, WA Computer Science ATAR Year 11 — Data Management — Module 65

Understanding BETWEEN and IN in a WHERE clause in ACARA Digital Technologies Years 9–10

What it does: This single IN list does the same job as yearLevel = 7 OR yearLevel = 9 OR yearLevel = 10. NOT IN keeps only rows whose value is not in the list, so Red and Blue students are left out. BETWEEN needs the word AND between the low and high values. A comma is a syntax error. BETWEEN is inclusive. Both the low value (100) and the high value (120) match. The list after IN must sit inside brackets, with the items separated by commas. NOT goes before IN, so the order is NOT IN. It excludes every value in the list.

Common mistakes: BETWEEN needs the low value first and the high value second. With 120 AND 100 no number can fit the range, so no rows come back. It should be BETWEEN 100 AND 120. BETWEEN joins its low and high limits with the keyword AND, not a comma. It should be BETWEEN 2 AND 10.

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 BETWEEN and IN in a WHERE clause to a class →

This module's key terms are also in a printable crossword (Modules 459–475) and word search (Modules 459–534).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which query returns students with 100 to 120 points, including both 100 and 120?

SELECT * FROM students WHERE points BETWEEN 100 AND 120;SELECT * FROM students WHERE points BETWEEN 100, 120;

Matches

WHERE price BETWEEN 5, 20

↓

BETWEEN needs the keyword AND between the low and high values, not a comma. It should be BETWEEN 5 AND 20.

Applying

Meanings

Term

BETWEEN low AND high

Meaning

Tests if a value is inside a range, including both limits. Its partner IN (a, b, c) checks a value against a list, replacing repeated OR. NOT IN excludes the list.

Practice

SELECT firstName, yearLevel, points

Testing

Gaps

WHERE points 90 AND 120;

Check Answer

Spots

3WHERE points

4BETWEEN 120 AND 100;

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

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 query returns students with 100 to 120 points, including both 100 and 120?

A. SELECT * FROM students WHERE points BETWEEN 100, 120;

B. SELECT * FROM students WHERE points BETWEEN 100 AND 120; ✓

BETWEEN needs the word AND between the low and high values. A comma is a syntax error.

2. A student has exactly 120 points. Does WHERE points BETWEEN 100 AND 120 include them?

A. No, BETWEEN stops just before 120

B. Yes, BETWEEN includes both limits ✓

BETWEEN is inclusive. Both the low value (100) and the high value (120) match.

3. Which WHERE clause does the same job as: yearLevel = 7 OR yearLevel = 9 OR yearLevel = 10?

A. WHERE yearLevel IN (7, 9, 10) ✓

B. WHERE yearLevel BETWEEN (7, 9, 10)

IN checks a value against a list and replaces repeated OR. BETWEEN only takes a low and a high value.

4. Which query finds students in the Red or Blue house using IN?

A. SELECT * FROM students WHERE house IN ('Red', 'Blue'); ✓

B. SELECT * FROM students WHERE house IN 'Red', 'Blue';

The list after IN must sit inside brackets, with the items separated by commas.

5. Which query leaves out products in the Tech and Sport categories?

A. SELECT * FROM products WHERE category NOT IN ('Tech', 'Sport'); ✓

B. SELECT * FROM products WHERE category IN NOT ('Tech', 'Sport');

NOT goes before IN, so the order is NOT IN. It excludes every value in the list.

Match the Pairs

WHERE mark BETWEEN 60 AND 80 → Keeps rows where mark is from 60 to 80, including both 60 and 80

WHERE house IN ('Red', 'Blue') → Keeps rows where house is Red or Blue, matching any value in the list

IN replaces repeated OR conditions on the same field → WHERE yearLevel IN (7, 9, 10)

NOT IN excludes every value in the list → WHERE house NOT IN ('Red', 'Blue')

WHERE price BETWEEN 5, 20 → BETWEEN needs the keyword AND between the low and high values, not a comma. It should be BETWEEN 5 AND 20.

WHERE house IN 'Red', 'Blue' → The list after IN must be inside brackets. It should be IN ('Red', 'Blue').

Key Term

BETWEEN low AND high

Tests if a value is inside a range, including both limits. Its partner IN (a, b, c) checks a value against a list, replacing repeated OR. NOT IN excludes the list.

Fill in the Blanks

1. SELECT firstName, points FROM students WHERE points BETWEEN 90 AND 120;

Hint: Type the keyword that tests whether a value is inside a range, including both limits.

2. SELECT firstName FROM students WHERE yearLevel BETWEEN 7 AND 8;

Hint: Type the keyword that separates the low limit from the high limit in a range test.

3. SELECT firstName, house FROM students WHERE house IN ('Red', 'Blue');

Hint: Type the operator that checks whether a value matches any item in a list, replacing repeated OR.

4. SELECT firstName, yearLevel FROM students WHERE yearLevel NOT IN (7, 8);

Hint: Type the two-word operator that excludes every value in the list.

5. SELECT name, price FROM products WHERE category IN ('Tech', 'Sport');

Hint: Type the column name that holds the type of product being checked against the list.

Spot the Error

Question 1

A SELECT firstName, points

B FROM students

C WHERE points

D BETWEEN 120 AND 100;

Line D — BETWEEN needs the low value first and the high value second. With 120 AND 100 no number can fit the range, so no rows come back. It should be BETWEEN 100 AND 120.

Question 2

A SELECT name, price

B FROM products

C WHERE price BETWEEN 2, 10;

Line C — BETWEEN joins its low and high limits with the keyword AND, not a comma. It should be BETWEEN 2 AND 10.

Question 3

A SELECT firstName, house

B FROM students

C WHERE house IN 'Red','Blue';

Line C — The list after IN must be inside brackets. It should be IN ('Red','Blue').

Question 4

A SELECT * FROM products

B WHERE stock IN NOT (0, 5);

C ORDER BY name;

Line B — The keywords are in the wrong order. NOT comes before IN, so it should be NOT IN (0, 5).

Question 5

A SELECT FROM products WHERE

B price BETWEEN 5 AND 20;

C ORDER BY price;

Line A — SELECT must say which fields to show. The * is missing, so it should be SELECT * FROM products WHERE.

Student with a class code? Join your class →