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, ...
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.
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
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.
Read on:- SQL Tutorial to enhance your knowledge!
Things to Note while using SQL BETWEEN Operator
- 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.
- 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.
- Negation with NOT: You can also use
NOT BETWEEN to retrieve rows that do not fall within the specified range.
- Complex Conditions: You can combine
BETWEEN with other SQL operators and conditions (e.g.,
OR) to create more complex filtering criteria.
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.
Check the UPDATE Query in SQL blog!
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:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
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)
(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.