SQL Between Operator

The SQL BETWEEN operator defines the ranges. It is a logical operator used in SQL to retrieve data within a specified range

For example, if we want to display the names of employees whose salaries are in a given range then we can use the SQL BETWEEN operator. The ranges can be defined as numeric, date, or text.

BETWEEN Operator in SQL

The BETWEEN operator in SQL is used to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. The BETWEEN operator includes begin and end values.

The BETWEEN operator can be used with SELECT, INSERT, UPDATE, and DELETE commands. This operator is particularly useful for filtering records based on a range of values

Syntax

The syntax of the SQL BETWEEN operator is:

WHERE column_name BETWEEN value1 AND value2;

It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN condition will return the records where the expression is within the range of value1 and value2.

SQL Between Operator Example

Let’s look at some examples of SQL BETWEEN Operator, to understand how to use it in SQL:

First we need to create a new database first and inside the database, we will create a table and then inside the table, we will apply the BETWEEN operator. Create new table and database using the following queries.

Demo Database

CREATE DATABASE GEEKSFORGEEKS;
USE GEEKSFORGEEKS;

CREATE TABLE STUDENTS(
     STUDENT_ID INT PRIMARY KEY,
     NAME VARCHAR(20),
     ADDRESS VARCHAR(20),
     AGE INT,
     DOB DATE);

INSERT INTO STUDENTS VALUES
    (1, 'DEV', '120', 2001, '2001-03-09'),
    (2, 'AMAN', '92', 2003, '2003-11-28'),
    (3, 'VINOD', '184', 2002, '2002-09-16'),
    (4, 'YASH', '87', 2001, '2001-06-18'),
    (5, 'NITYA', '195', 2000, '2000-01-12');

SELECT * FROM STUDENTS;

Output:

Table’s data

Using BETWEEN Operator Example

In this example we only want to display the details of students having TOTAL_MARKS between 100-200 and DOB between 2001-01-01 and 2003-01-01.

Query:

 SELECT *
 FROM STUDENTS
 WHERE TOTAL_MARKS BETWEEN 100 AND 200 AND DOB BETWEEN "2001-01-01" AND "2003-01-01";

Final result

Important Points About SQL BETWEEN Operator

1. The BETWEEN operator is a logical operator used in SQL to retrieve data within a specified range. The range is inclusive, meaning it includes the start and end values.
2. The BETWEEN operator can be used with SELECT, INSERT, UPDATE, and DELETE commands to filter records based on a range of values.
3. The values in the range can be of textual, numeric, or date data types.
4. The NOT BETWEEN operator can be used to retrieve data that falls outside the specified range.
5. The BETWEEN operator can be combined with the IN operator to further refine the query and select values within a range that also match a list of values.
6. The BETWEEN operator simplifies queries that would otherwise require multiple greater than and less than conditions.

SQL BETWEEN Operator- FAQs

Why do we use SQL BETWEEN operator?

We use the BETWEEN operator in SQL when we want to select values within a given range.

In how many ways can we define the range?

We can define ranges in as either numeric, date, text.

Can we find ranges withing a date and time using BETWEEN the ?

Yes, we can find ranges withing mentioned date & time using BETWEEN statement.

How to get data between two timestamps in SQL?

The difference between the start and end timestamps is calculated by using the following query: SELECT TIMESTAMPDIFF(SECOND, start_timestamp, end_timestamp) FROM events WHERE event_name = ‘Meeting’;