SQL Between

We use the BETWEEN keyword to define the ranges. In SQL sometimes there is a need to display the records which occur in some ranges. For example, if we want to display names of employees whose salaries are in a given range then we can use the SQL between command which is used to select values within a given range. The ranges can be defined as numeric, date, or text. In this article, we will learn about how to use SQL Between Command.

SQL BETWEEN Statement

The SQL BETWEEN condition allows you 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.

Syntax of SQL Between

Below is the syntax of SQL BETWEEN statement.

SELECT column_name(s)
FROM table_name
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.

Example of SQL Between Command

To know how to apply SQL Between command on records of a table then kindly follow the below steps:

Step 1: To get started 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 clause. the CREATE DATABASE command will used to create a new database.

Query:

CREATE DATABASE GEEKSFORGEEKS;

Creating new database

Step 2 : Now we will use or select the database after creating it. We use the USE command in order to select our database and this step is necessary to tell SQL in which database we are working.

Query:

USE GEEKSFORGEEKS;

Using the database

Step 3: As we have selected our database now we will create a new table in it. To create a table we use CREATE TABLE command. As an example we are creating a table Students and then we will look at the structure of the table also. After that we will insert some data in it.

Query:

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

DESC STUDENTS

Creating a table

Step 4: After creating the table we will add few entries in it and then we will use the BETWEEN command to filter out data from the table within a given range only. We use the INSERT INTO command to insert data into a table.

Query:

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

Inserting data

Step 5: we will look at the table’s data using the SELECT command to see the table.

Query:

SELECT * FROM STUDENTS;

Table’s data

Step 6: Now we will apply BETWEEN command. Let’s take an example that 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

Conclusion

In this article we have learnt about the SQL BETWEEN command. To summarize the article, SQL between command is used to select values within a given range. The ranges can be defined as either numeric, date, text. We use the BETWEEN keyword to define the ranges. If you are also looking forward to apply the BETWEEN statement in your database then kindly follow the above mentioned steps.

FAQs on SQL Between

Q.1: Why do we use SQL BETWEEN command?

Answer :

We use the BETWEEN command when we want to select values within a given range.

Q.2: In how many ways can we define the range?

Answer:

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

Q.3: Can we find ranges withing a date and time using BETWEEN statement?

Answer:

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

Q.4: How to get data between two timestamps in SQL?

Answer:

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’;