Module 8: Range Filtering with BETWEEN and NOT BETWEEN

Module 8

Range Filtering with BETWEEN and NOT BETWEEN

A step-by-step, hands-on SQL practice lesson — with a live SQL playground

SELECT student_name, grade_level FROM class_room WHERE grade_level BETWEEN 8 AND 9;

After learning how to filter rows with WHERE, I can use BETWEEN when I need a clean way to work with a range of values. In this lesson, I practice both BETWEEN and NOT BETWEEN with numbers, dates, and a simple class_room table. I will build the database step by step, test each query, and use the live playground to see exactly which rows are inside or outside a range.

Contents

1. Introduction
2. Requirements
3. Practice: Step by Step
4. Common Problems & Solutions
5. Conclusion
6. Homework & Quiz
7. Live SQL Playground
8. Related Lessons
9. FAQ

Introduction

What You'll Learn — and Why Range Filtering Matters

I use range filters whenever I need to ask a question such as “which students are in grades 8 through 9?” or “which records fall between two dates?” The BETWEEN operator makes these questions easy to read, while NOT BETWEEN lets me find values outside the range.

🎯 Objective

By the end of this lesson, you will be able to:

• Explain how BETWEEN works in SQL.
• Use BETWEEN to filter numeric values inside an inclusive range.
• Use NOT BETWEEN to find values outside an inclusive range.
• Use range filters with dates and understand why date/time boundaries need care.
• Combine BETWEEN with AND, OR, and other WHERE conditions.
• Recognize common mistakes and choose clearer range conditions.

⚠️ What to Avoid

Range filters need exact boundaries

• Do not forget that BETWEEN is inclusive: both boundary values are included.
• Do not assume NOT BETWEEN includes NULL. A NULL value does not become an ordinary value outside the range.
• Be careful with date/time columns. A condition such as BETWEEN '2026-08-01' AND '2026-08-31' can be surprising when the column contains times.
• Do not use a range when a different condition communicates your business rule more clearly.

💡 Important to Know

The core idea

BETWEEN low AND high means the value is greater than or equal to low and less than or equal to high. NOT BETWEEN low AND high reverses that range condition and keeps values outside the inclusive boundaries.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, Linux, or another operating system that can run MySQL. The SQL syntax in this lesson is the same across these systems.

Database ServerMySQL installed and running. If you need installation help, see How to Install MySQL Step by Step for Beginners.

Optional: XAMPPXAMPP plus phpMyAdmin provides a visual way to run the SQL. See How to Install XAMPP Step by Step for Beginners (Complete Guide 2026).

Prior knowledgeYou should know basic SELECT and WHERE. If you need a refresher, review Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide).

No installation is required for the browser playground below. The real practice steps are written for MySQL or phpMyAdmin.

Practice

Step-by-Step Practice: BETWEEN and NOT BETWEEN

I recommend following the steps in order. First I prepare the database, then I test a simple numeric range, and finally I combine range filtering with other conditions and dates.

1 Open MySQL or phpMyAdmin

On Windows, macOS, or Linux, open your MySQL client. If you use XAMPP, start MySQL and open phpMyAdmin. In the terminal, I can connect with:

mysql -u root -p

The basic range-filtering syntax is:

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

For the opposite range:

SELECT column1, column2
FROM table_name
WHERE column_name NOT BETWEEN low_value AND high_value;

2 Create a Database and Verify It

I create a separate practice database so my range-filtering exercises stay organized.

CREATE DATABASE module8_between_practice;
SHOW DATABASES;
USE module8_between_practice;

After SHOW DATABASES;, confirm that module8_between_practice appears.

3 Create the class_room Table

Now I create a small classroom table with values that make range filtering easy to see.

CREATE TABLE class_room (
  id INT PRIMARY KEY,
  student_name VARCHAR(80),
  grade_level INT,
  score DECIMAL(5,2),
  status VARCHAR(20),
  room VARCHAR(20)
);

Verify the table structure:

SHOW TABLES;
DESCRIBE class_room;

4 Insert Sample Data

I insert different grade levels and scores so we can test both sides of a range.

INSERT INTO class_room (id, student_name, grade_level, score, status, room) VALUES
(1, 'Dara', 7, 62.50, 'active', 'A101'),
(2, 'Sokha', 8, 74.00, 'inactive', 'A101'),
(3, 'Maly', 8, 81.50, 'active', 'B201'),
(4, 'Vannak', 9, 88.00, 'pending', 'B201'),
(5, 'Lina', 9, 93.50, 'active', 'C301'),
(6, 'Rith', 10, 69.00, 'inactive', 'C301'),
(7, 'Nita', 10, 97.00, 'active', 'D401'),
(8, 'Bora', 11, 55.00, 'pending', 'D401');

SELECT * FROM class_room;

5 Use BETWEEN with Numbers

Suppose I want students in grades 8 through 9. I can write:

SELECT student_name, grade_level
FROM class_room
WHERE grade_level BETWEEN 8 AND 9;

The important detail is that 8 and 9 are included. In other words, this is equivalent to:

SELECT student_name, grade_level
FROM class_room
WHERE grade_level >= 8
  AND grade_level <= 9;

My practice note

When I see BETWEEN 8 AND 9, I mentally read it as “greater than or equal to 8, and less than or equal to 9.” That makes the inclusive boundary easier to remember.

6 Use NOT BETWEEN for Values Outside a Range

Now I reverse the range. If I want students whose grades are outside 8 through 9:

SELECT student_name, grade_level
FROM class_room
WHERE grade_level NOT BETWEEN 8 AND 9;

This is conceptually the same as:

SELECT student_name, grade_level
FROM class_room
WHERE grade_level < 8
   OR grade_level > 9;

7 Filter a Score Range

BETWEEN is not limited to integer columns. I can use it with decimal scores too.

SELECT student_name, score
FROM class_room
WHERE score BETWEEN 80 AND 90
ORDER BY score;

And I can find scores outside that interval:

SELECT student_name, score
FROM class_room
WHERE score NOT BETWEEN 80 AND 90
ORDER BY score;

8 Combine a Range with Another Condition

Real queries often need more than one filter. For example, I can find active students with scores from 70 through 95.

SELECT student_name, score, status
FROM class_room
WHERE status = 'active'
  AND score BETWEEN 70 AND 95
ORDER BY score DESC;

I can also combine NOT BETWEEN with another condition:

SELECT student_name, score, status
FROM class_room
WHERE status = 'active'
  AND score NOT BETWEEN 80 AND 90
ORDER BY score;

9 Practice with Dates and Understand the Boundary

Range filtering is also useful for dates. For a DATE column, this is straightforward:

SELECT *
FROM your_table
WHERE enrollment_date BETWEEN '2026-08-01' AND '2026-08-31';

For a DATETIME column, I need to think about the time portion. A safer pattern for the whole month is often:

WHERE created_at >= '2026-08-01'
  AND created_at < '2026-09-01'

This avoids accidentally excluding records later on August 31 when the column stores a time as well as a date.

Troubleshooting

5 Common Problems and Solutions

Problem 1: I thought BETWEEN excluded the boundary values

I expected BETWEEN 8 AND 9 to return only values greater than 8 and less than 9.

SolutionRemember that BETWEEN is inclusive. BETWEEN 8 AND 9 includes both 8 and 9. If you need strict boundaries, use > and < explicitly.

Problem 2: NOT BETWEEN does not return NULL rows

I may think a NULL is “outside the range,” but SQL does not treat NULL as a normal number.

SolutionIf NULL should be included in the result, say so explicitly, for example: WHERE score NOT BETWEEN 80 AND 90 OR score IS NULL.

Problem 3: My date query misses records from the last day

This commonly happens when the column is a DATETIME rather than a plain DATE.

SolutionFor a complete date interval, consider a half-open range such as created_at >= '2026-08-01' AND created_at < '2026-09-01'.

Problem 4: I used BETWEEN with values in the wrong order

A condition such as BETWEEN 90 AND 80 does not mean “between 80 and 90” in the normal way.

SolutionPut the lower boundary first and the higher boundary second: BETWEEN 80 AND 90.

Problem 5: My combined AND/OR range query is confusing

Once several conditions are combined, it can become difficult to see which part is supposed to be inside or outside the range.

SolutionUse parentheses for grouped logic, keep each condition on its own line, and test each condition separately before combining them.

Conclusion

What I Want You to Remember

The main lesson I take from Module 8 is simple: BETWEEN is a readable way to filter values inside an inclusive range, while NOT BETWEEN filters values outside that range.

My biggest habit here is checking the boundaries before I run the query. I ask: Are the lower and upper values supposed to be included? For numeric columns, BETWEEN is often very clear. For date/time columns, I also check whether the column stores only a date or a date plus a time.

Expected result from this lesson

You should now be able to use BETWEEN and NOT BETWEEN confidently, explain their inclusive boundaries, combine them with other filters, and avoid common date/time and NULL mistakes.

Next lesson: continue building SQL conditions by combining logical operators and more advanced filtering patterns.

Homework

Homework & Quiz

📝 Homework Task

Using the class_room table, write these queries yourself before checking the live playground:

1. Find students whose grade level is between 8 and 10.
2. Find students whose grade level is not between 8 and 9.
3. Find students whose score is between 75 and 95.
4. Find active students whose score is not between 80 and 90.
5. Write a query for a hypothetical created_at DATETIME column that returns every record from August 2026.

🧪 Quiz: Test Your Understanding (10 Questions)

Pick one answer per question, then click Submit to see your score instantly.

1. What does BETWEEN 8 AND 9 include?

Only values greater than 8 and less than 9 Values from 8 through 9, including both boundaries Only 8 Only 9

2. Which syntax correctly filters a score from 80 to 90?

WHERE score IN 80 AND 90 WHERE score RANGE 80, 90 WHERE score BETWEEN 80 AND 90 WHERE score FROM 80 TO 90

3. What does NOT BETWEEN 80 AND 90 mean?

Values outside the inclusive 80–90 range Only values equal to 80 or 90 Values between 80 and 90 only Values greater than 80 only

4. Is BETWEEN inclusive in SQL?

Never Only for text Only in phpMyAdmin Yes, both endpoints are included

5. What is a useful equivalent for score BETWEEN 80 AND 90?

score > 80 OR score < 90 score >= 80 AND score <= 90 score = 80 AND score = 90 score <= 80 AND score >= 90

6. Does NOT BETWEEN automatically include NULL values?

Yes, always Only when the column is numeric No Only on macOS

7. Which is generally safer for a full month on a DATETIME column?

created_at >= '2026-08-01' AND created_at < '2026-09-01' created_at = '2026-08' created_at BETWEEN '2026-08-01' AND '2026-08-31' created_at IN '2026-08'

8. Which order should the BETWEEN boundaries normally have?

High value first Random order Text before number Lower value first, then higher value

9. Which condition finds active students with scores between 70 and 95?

WHERE status = 'active' OR score BETWEEN 70 AND 95 WHERE status BETWEEN 'active' AND score WHERE status = 'active' AND score BETWEEN 70 AND 95 WHERE active AND BETWEEN score 70, 95

10. What is the main idea of Module 8?

BETWEEN creates a table BETWEEN selects values inside an inclusive range, while NOT BETWEEN selects values outside it BETWEEN only works with text NOT BETWEEN sorts results

0 / 10

Try It Live

🧪 Live SQL Playground

This browser playground uses SQLite through WebAssembly. It is designed to practice the range-filtering logic in this lesson; use your real MySQL server for MySQL-specific testing.

class_roomid INTEGER PK
student_name TEXT
grade_level INTEGER
score REAL
status TEXT
room TEXT

— Load an example query — List all students BETWEEN grades 8–9 NOT BETWEEN grades 8–9 BETWEEN scores 80–90 NOT BETWEEN scores 80–90 Active + score BETWEEN 70–95 Active + NOT BETWEEN 80–90 BETWEEN + another filter

SELECT * FROM class_room;

Loading SQL engine…

Run a query to see results here.

Related Lessons

Continue Learning SQL

Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide)

How to Install XAMPP Step by Step for Beginners (Complete Guide 2026)

How to Install MySQL Step by Step for Beginners

FAQ

Frequently Asked Questions

1. What does BETWEEN do in SQL?

BETWEEN filters values that fall within a range, including both boundary values.

2. Is BETWEEN inclusive?

Yes. BETWEEN low AND high includes both low and high.

3. What does NOT BETWEEN mean?

It returns values outside the inclusive range specified by the two boundaries.

4. Can BETWEEN work with decimal numbers?

Yes. It can be used with numeric values such as integer and decimal columns.

5. Can BETWEEN work with dates?

Yes. It can filter date values, but DATETIME columns require extra care because the time component is part of the value.

6. Does NOT BETWEEN include NULL values?

Not automatically. NULL needs explicit handling with IS NULL if your business rule requires it.

7. What is the difference between BETWEEN and IN?

BETWEEN describes a continuous range, while IN checks membership in a list of specific values.

8. Can I combine BETWEEN with AND?

Yes. For example, status = 'active' AND score BETWEEN 70 AND 95 applies both conditions.

9. What happens if I reverse the BETWEEN boundaries?

Use the lower boundary first and the higher boundary second. Do not rely on reversed boundaries to express a normal range.

10. Is the live playground the same as MySQL?

No. The playground uses SQLite compiled to WebAssembly. It is for browser practice; use MySQL or phpMyAdmin for MySQL-specific testing.

Part of the CodeMend SQL Module series. Keep practicing inclusive ranges, NOT BETWEEN, NULL handling, and date/time boundaries.