Categories
28°C, New York
Home SQL SQL BETWEEN Operator

SQL BETWEEN Operator

SQL | Except Clause

SQL | ALL and ANY

SQL BETWEEN operator select the value in the given range, including the start and end values.

BETWEEN Operator in SQL

The BETWEEN operator in SQL is used to get the values present in the given range. The values can be text, date, or numbers.

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

SQL BETWEEN Syntax is:

BETWEEN value1 AND value2;

SQL Between Operator Example

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

First, we need to create a new database and a table inside the database. We will then use the BETWEEN operator in SQL query.

Demo Database

SQL 
CREATE DATABASE w3wiki;
USE w3wiki;

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

SQL BETWEEN Example

In this example, we will 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";

Output:

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 matches 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 the 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 within a date and time using BETWEEN the?

Yes, we can find ranges within 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’;


Tags:
#sql-operators #SQL

SQL | Except Clause

SQL | ALL and ANY