SQL BETWEEN Operator(With Syntax and Examples)

The BETWEEN operator in SQL serves as a tool for refining result sets by focusing on values within a predefined range in a particular column. It facilitates the extraction of rows whose column values lie within a specified range, encompassing both the lower and upper limits. SQL queries frequently employ the BETWEEN operator to streamline the retrieval of data that adheres to specific conditions.

SQL BETWEEN Syntax:

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

• column1, column2, ...: The columns you want to retrieve in the result set.
• table_name: The name of the table you are querying.
• column_name: The name of the column you want to apply the BETWEEN operator to.
• value1 and value2: The lower and upper bounds of the range you want to filter on.

Usage Example: Suppose you have a table named employees with a column called, and you want to retrieve all employees with salaries between $40,000 and $60,000:

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

This SQL query will return all rows from the employees table where the salary falls within the specified range.

Things to Note while using SQL BETWEEN Operator

1. Inclusive Range: The BETWEEN operator is inclusive, meaning it includes rows where the column value is equal to the lower and upper bounds. In the example above, employees with salaries of $40,000 and $60,000 would be included in the result.
2. Data Types: The values used with the BETWEEN operator must be of compatible data types with the column being compared. For example, if you’re comparing dates, the values should be in a date format that the database can recognize.
3. Negation with NOT: You can also use NOT BETWEEN to retrieve rows that do not fall within the specified range.
4. Complex Conditions: You can combine BETWEEN with other SQL operators and conditions (e.g., AND, OR) to create more complex filtering criteria.

The SQL BETWEEN operator simplifies the process of selecting rows within a specific range of values, making it a valuable tool for querying databases and retrieving relevant data for analysis or reporting purposes.

Here’s a step-by-step guide with SQL code for creating a database, or table, inserting data, and using the BETWEEN operator:

Step 1: Create a Simple New Database in SQL

To create a new database in SQL, you can use the following code:

CREATE DATABASE MyDatabase;

This SQL command will create a new database named “MyDatabase.”

Step 2: Create a New Table in SQL

Next, let’s create a new table within the “MyDatabase” database. Suppose we want to create a table to store employee information:

USE MyDatabase;

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2)
);

This code creates a table named “Employees” with columns for EmployeeID, FirstName, LastName, and Salary.

Step 3: Insert Data into the Table in SQL

Now, let’s insert some sample data into the “Employees” table:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES
(1, 'John', 'Doe', 50000.00),
(2, 'Jane', 'Smith', 60000.00),
(3, 'Alice', 'Johnson', 45000.00),
(4, 'Bob', 'Brown', 55000.00);

This SQL code inserts four records into the “Employees” table.

Step 4: View the Inserted Data in SQL

To view the data you’ve inserted, you can use a SELECT statement:

SELECT * FROM Employees;

This SQL query will display all the records in the “Employees” table.

Step 5: Use the BETWEEN Operator in SQL

Now, let’s use the BETWEEN operator to retrieve employees with salaries between $45,000 and $55,000:

SELECT * FROM Employees
WHERE Salary BETWEEN 45000.00 AND 55000.00;

This SQL query will return the employees whose salaries fall within the specified range.

These steps demonstrate how to create a database, create a table, insert data, and use the BETWEEN operator in SQL.