SQL BETWEEN Operator
The SQL BETWEEN
operator is used to filter the result set within a specific range. It is often used with WHERE
clauses to select values within a range, such as numbers, dates, or text.
The BETWEEN
operator is inclusive, meaning it includes the boundary values.
In this tutorial, we will go through SQL BETWEEN Operator, its syntax, and how to use this operator in SQL statements, with the help of well detailed examples.
Syntax of SQL BETWEEN Operator
The basic syntax of the SQL BETWEEN
operator is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Each part of this syntax has a specific purpose:
- SELECT: Specifies the columns to retrieve.
- FROM: Specifies the table from which to retrieve data.
- WHERE column_name BETWEEN value1 AND value2: Filters the rows where
column_name
is betweenvalue1
andvalue2
, inclusive of both boundary values.
Step-by-Step Examples with MySQL
We’ll go through various examples demonstrating the BETWEEN
operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students
table with fields id
, name
, age
, grade
, and locality
.
Setup for Examples: Creating the Database and Table
1. Open MySQL Workbench and create a new database:
CREATE DATABASE school;
2. Select the school
database:
USE school;
3. Create a students
table:
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
grade VARCHAR(10),
locality VARCHAR(50)
);
4. Insert sample data into the students
table:
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');
Examples: Using BETWEEN Operator in Queries
Now, let’s explore different scenarios of using the BETWEEN
operator with this table.
1. Filtering by Age Range
To select students who are between the ages of 14 and 15:
SELECT name, age
FROM students
WHERE age BETWEEN 14 AND 15;
This query returns the names and ages of students who are between 14 and 15 years old, inclusive of 14 and 15.
2. Using NOT BETWEEN to Exclude a Range
To select students who are not between the ages of 14 and 15:
SELECT name, age
FROM students
WHERE age NOT BETWEEN 14 AND 15;
This query returns students whose age is outside the range of 14 to 15, excluding both boundary values.
Reference: SQL NOT Operator
FAQs for SQL BETWEEN
1. What does the SQL BETWEEN operator do?
The BETWEEN
operator filters results to include values within a specified range, including the boundary values.
2. Can BETWEEN be used with text data?
Yes, BETWEEN
can be used with text data to filter results based on alphabetical order.
3. How is BETWEEN different from using greater than or less than conditions?
BETWEEN
is more concise and includes the boundary values, whereas combining greater than and less than conditions requires additional operators to include boundaries.
4. Can I use NOT BETWEEN to exclude a range?
Yes, NOT BETWEEN
excludes values within the specified range and only includes values outside the boundary values.
5. Does BETWEEN include NULL values?
No, BETWEEN
does not include NULL
values. NULL
values must be handled separately if needed.