Sunday, August 30, 2026

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.

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?

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

3. What does NOT BETWEEN 80 AND 90 mean?

4. Is BETWEEN inclusive in SQL?

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

6. Does NOT BETWEEN automatically include NULL values?

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

8. Which order should the BETWEEN boundaries normally have?

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

10. What is the main idea of Module 8?

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
Loading SQL engine…
Run a query to see results here.

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.

By - No comments:
Tags , , ,

Module 7 Logical Inversion with NOT

MODULE 7: LOGICAL INVERSION WITH NOT Self-contained article for Blogger — paste into "HTML view" Includes: live SQL playground (sql.js/WebAssembly), black/white code blocks, and a JS-graded 10-question quiz with blue tick marks. ============================================================ -->
Module 7

Logical Inversion with NOT

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

SELECT * FROM class_room WHERE NOT status = 'active';

In the earlier modules, I learned how to filter rows with WHERE and comparison operators. In this lesson, I use NOT to reverse a condition: instead of asking SQL to find what matches, I can ask it to find what does not match. I will practice this idea with a simple class_room table, work through each query step by step, and use the live playground to see the result immediately.

Introduction

What You'll Learn — and Why NOT Matters

I like NOT because it gives a SQL query a simple but powerful change of direction. Instead of saying “give me rows where this condition is true,” I can say “give me rows where this condition is not true.” Once I understood that idea, filtering data became much easier to reason about.

🎯 Objective

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

  • Explain what the SQL NOT operator does.
  • Use NOT with =, IN, LIKE, BETWEEN, and grouped conditions.
  • Read a NOT condition from left to right and predict its result.
  • Create and verify a practice database and a class_room table.
  • Recognize common mistakes and write clearer negative conditions.

⚠️ What to Avoid

Negative conditions need careful thinking
  • Do not add NOT simply because the sentence contains the word “not.” First identify the exact condition you want to reverse.
  • Do not confuse NOT status = 'active' with a completely different business rule involving NULL.
  • Do not assume NOT makes NULL values behave like ordinary values. SQL uses three-valued logic, so NULL needs special handling.
  • When a condition becomes complicated, use parentheses so the intended logic is obvious.

💡 Important to Know

The core idea

NOT reverses a Boolean condition. For example, WHERE NOT status = 'active' asks for rows whose status = 'active' condition is not true. For a nullable column, that does not automatically mean “include NULL rows.”

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, Linux, or another operating system that can run MySQL. The SQL syntax is the same for this lesson.
Database ServerMySQL installed and running. If you need installation help, see How to Install MySQL Step by Step for Beginners.
Optional: XAMPPXAMPP plus phpMyAdmin gives you a visual interface for creating databases and running SQL. See How to Install XAMPP Step by Step for Beginners (Complete Guide 2026).
Prior knowledgeYou should be comfortable with SELECT and WHERE. Review Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide) if needed.

No installation is required for the live playground below. It runs in your browser, while the step-by-step practice is designed for your own MySQL server.

Practice

Step-by-Step Practice: Logical Inversion with NOT

I recommend doing these steps in order. I start by opening MySQL, create a small database, verify the table, and then gradually make the NOT queries more useful.

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 general syntax pattern is:

SELECT column1, column2
FROM table_name
WHERE NOT condition;

2 Create a Database and Verify It

I create a separate practice database so my exercises do not interfere with other projects.

CREATE DATABASE module7_not_practice;
SHOW DATABASES;
USE module7_not_practice;

After SHOW DATABASES;, check that module7_not_practice appears in the list.

3 Create the class_room Table

Now I create the example table requested for this lesson. It represents a small classroom list.

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

Verify both the table and its columns:

SHOW TABLES;
DESCRIBE class_room;

4 Insert Sample Classroom Data

I use different statuses, grade levels, and rooms so that negative filters have something meaningful to return.

INSERT INTO class_room (id, student_name, grade_level, status, room) VALUES
(1, 'Dara', 7, 'active', 'A101'),
(2, 'Sokha', 8, 'inactive', 'A101'),
(3, 'Maly', 7, 'active', 'B201'),
(4, 'Vannak', 9, 'pending', 'B201'),
(5, 'Lina', 8, 'active', 'C301'),
(6, 'Rith', 9, 'inactive', 'C301'),
(7, 'Nita', 10, 'active', 'D401'),
(8, 'Bora', 10, 'pending', 'D401');
SELECT * FROM class_room;

5 Basic NOT with Equality

First, I ask for students whose status is not active.

SELECT student_name, status
FROM class_room
WHERE NOT status = 'active';

You can often express the same simple idea with <>:

SELECT student_name, status
FROM class_room
WHERE status <> 'active';
My practice note

I use NOT when I want to make the logical inversion obvious. I use <> when a simple “not equal” comparison is clearer.

6 Use NOT IN for Several Values

Suppose I want students who are not in rooms A101 or B201.

SELECT student_name, room
FROM class_room
WHERE room NOT IN ('A101', 'B201');

This is easier to read than writing several separate “not equal” conditions.

7 Use NOT LIKE for Text Patterns

I can also reverse a pattern match. For example, find names that do not start with the letter D.

SELECT student_name
FROM class_room
WHERE student_name NOT LIKE 'D%';

Here, % means any sequence of characters. NOT LIKE keeps rows that do not match that pattern.

8 Use NOT BETWEEN for a Range

Now I reverse a range condition. I want students whose grade level is outside 8 through 9.

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

Because BETWEEN includes its endpoints, NOT BETWEEN 8 AND 9 excludes 8 and 9 and returns values outside that inclusive range.

9 Combine NOT with Another Condition

This is where the lesson becomes more realistic. I can ask for active students whose room is not A101.

SELECT student_name, status, room
FROM class_room
WHERE status = 'active'
  AND NOT room = 'A101'
ORDER BY student_name;

Parentheses become especially useful when I combine NOT with AND and OR:

SELECT student_name, status, room
FROM class_room
WHERE NOT (status = 'inactive' OR room = 'A101');

Troubleshooting

5 Common Problems and Solutions

Problem 1: I used NOT, but the query feels confusing

For example, WHERE NOT status = 'active' can be harder to scan than a simple inequality.

SolutionChoose the clearest expression for the job. For a single equality check, status <> 'active' may be easier to read. Use NOT when it makes the logical reversal clearer or when you are negating a larger condition.

Problem 2: I expected NULL rows to appear

A nullable status column may contain NULL, but WHERE NOT status = 'active' does not treat NULL as an ordinary non-active value.

SolutionIf your business rule says NULL should also be included, say so explicitly: WHERE status <> 'active' OR status IS NULL.

Problem 3: NOT IN behaves unexpectedly

NULLs inside the compared data or inside a NOT IN list can make three-valued SQL logic surprising.

SolutionCheck for NULL explicitly when necessary, and avoid putting NULL in a NOT IN list unless you understand the resulting logic.

Problem 4: My AND/OR result is not what I expected

Without parentheses, a negative condition can be difficult to read and reason about.

SolutionUse parentheses around the condition you intend to reverse, for example WHERE NOT (status = 'inactive' OR room = 'A101').

Problem 5: I wrote NOT for a NULL check

A common mistake is writing something like WHERE NOT status = NULL.

SolutionUse the dedicated NULL syntax: WHERE status IS NOT NULL. SQL does not compare NULL with ordinary = or <> logic in the way beginners often expect.

Conclusion

What I Want You to Remember

After practicing this lesson, the main idea is simple: NOT reverses a condition. I can use it directly, or through forms such as NOT IN, NOT LIKE, and NOT BETWEEN.

The most important habit I developed here is to read a negative condition carefully. I ask myself: What exact condition am I reversing? Then I check how NULL, AND, and OR affect the result.

Expected result from this lesson

You should now be able to write negative filters confidently, explain why they return particular rows, and choose between NOT, <>, NOT IN, NOT LIKE, and NOT BETWEEN based on the problem.

Next lesson: build more advanced conditions by combining logical operators and grouping expressions carefully.

Homework

Homework & Quiz

📝 Homework Task

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

  1. Find students whose status is not active.
  2. Find students who are not in rooms A101 or C301.
  3. Find students whose name does not start with B.
  4. Find students whose grade is not between 8 and 9.
  5. Find active students who are not in room D401.

🧪 Quiz: Test Your Understanding (10 Questions)

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

1. What does NOT do to a condition?

2. Which query finds rows where status is not active?

3. Which operator is designed to exclude several listed values?

4. What does NOT LIKE 'D%' mean?

5. What does NOT BETWEEN 8 AND 9 exclude?

6. Does NOT status = 'active' automatically include NULL status values?

7. Which syntax is correct for finding non-NULL status values?

8. Why are parentheses useful with a complex NOT condition?

9. Which query excludes rooms A101 and B201?

10. Which statement is the best general description of this lesson?

0 / 10

Try It Live

🧪 Live SQL Playground

This browser playground uses SQLite through WebAssembly. It is designed for practicing the logic of this lesson; your real MySQL practice should be done in MySQL or phpMyAdmin.

class_roomid INTEGER PK
student_name TEXT
grade_level INTEGER
status TEXT
room TEXT
Loading SQL engine…
Run a query to see results here.

FAQ

Frequently Asked Questions

1. What does NOT mean in SQL?

NOT reverses a logical condition.

2. Is NOT the same as <>?

For a simple equality comparison, NOT column = value and column <> value express the same basic idea.

3. What is NOT IN used for?

NOT IN excludes rows whose value matches any value in a supplied list.

4. What is NOT LIKE used for?

NOT LIKE keeps values that do not match a text pattern.

5. What is NOT BETWEEN used for?

NOT BETWEEN keeps values outside an inclusive range.

6. Does NOT include NULL values?

Not automatically. Use IS NULL or IS NOT NULL for explicit NULL logic.

7. How should I use NOT with AND and OR?

Use parentheses to make the condition being negated explicit.

8. Can NOT be used with text?

Yes. It can reverse conditions involving text comparisons, LIKE, IN, and other Boolean expressions.

9. Can I use NOT in UPDATE or DELETE?

Yes. NOT can appear in a WHERE clause of statements such as UPDATE and DELETE. Be especially careful with DELETE.

10. Is the live playground the same as MySQL?

No. The playground uses SQLite compiled to WebAssembly. It is for browser practice; MySQL-specific behavior should be tested on a real MySQL server.

Part of the CodeMend SQL Module series. Keep practicing negative conditions, especially NULL handling and grouped AND/OR logic.

By - No comments:
Tags , , ,
Subscribe to: Posts (Atom)