SQL BETWEEN Operator

Introduction

In this chapter, we will learn how to use the BETWEEN operator in SQL. The BETWEEN operator is used to filter the result set within a specified range. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the BETWEEN operator effectively.

What is the BETWEEN Operator?

The BETWEEN operator is used to select values within a given range. The range can be numeric, textual, or date values. The BETWEEN operator includes the start and end values in the range.

Syntax for BETWEEN

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• column_name: The column to be tested for values within the specified range.
• value1 AND value2: The range of values to select.

Example

Assume we have a table named employees:

SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 60000;

This command retrieves the first_name, last_name, and salary columns from the employees table where the salary is between 50000 and 60000, inclusive.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Using the BETWEEN Operator

To retrieve employees whose salary is between 50000 and 60000:

SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 60000;

Output

first_name	last_name	salary
Ramesh	Kumar	50000
Sita	Patel	60000
Arjun	Singh	55000
Ramesh	Kumar	50000

4. Using NOT BETWEEN

The NOT BETWEEN operator is used to filter records that do not fall within a specified range.

Example

To retrieve employees whose salary is not between 50000 and 60000:

SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 60000;

Output

first_name	last_name	salary
Priya	Sharma	62000

Using BETWEEN with Dates

The BETWEEN operator can also be used to filter date ranges.

Example

Assume we add a hire_date column to the employees table:

ALTER TABLE employees ADD hire_date DATE;

UPDATE employees
SET hire_date = '2022-01-15' WHERE id = 1;
UPDATE employees
SET hire_date = '2022-02-20' WHERE id = 2;
UPDATE employees
SET hire_date = '2022-03-05' WHERE id = 3;
UPDATE employees
SET hire_date = '2022-03-15' WHERE id = 4;
UPDATE employees
SET hire_date = '2022-04-01' WHERE id = 5;

To retrieve employees hired between '2022-01-01' and '2022-03-31':

SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-03-31';

Output

first_name	last_name	hire_date
Ramesh	Kumar	2022-01-15
Sita	Patel	2022-02-20
Arjun	Singh	2022-03-05
Priya	Sharma	2022-03-15

Using BETWEEN with Text

The BETWEEN operator can also be used with text values. It compares the values based on their ASCII values.

Example

To retrieve employees whose first_name is between 'A' and 'M':

SELECT first_name, last_name
FROM employees
WHERE first_name BETWEEN 'A' AND 'M';

Output

first_name	last_name
Arjun	Singh
Priya	Sharma

Conclusion

The BETWEEN operator is used for filtering records within a specified range. This chapter covered the basic syntax, using BETWEEN with numeric values, dates, and text values, and using NOT BETWEEN to exclude values outside the specified range. Understanding how to use the BETWEEN operator effectively will enhance your ability to query and analyze your database data.