When working with databases, filtering data based on certain criteria is a common requirement. PL/SQL offers various operators to facilitate this, with one of the most effective being the BETWEEN operator.
This operator enables us to select values that lie in a specific range whether those values are numbers, dates or text. In this article, We will learn about PL/SQL BETWEEN Operator in detail by understanding various examples and so on.
What is the PL/SQL BETWEEN Operator?
The BETWEEN operator in PL/SQL is used to filter records where a column's value falls within a specific range. His range includes both the start and end values.
It is a concise way to specify a range for our queries, reducing the need for multiple comparison operators like >= and <=.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Breakdown
- column_name(s): The column(s) you want to retrieve.
- table_name: The table from which you are selecting the data.
- column_name: The column you are applying the BETWEEN operator to.
- value1: The lower bound of the range.
- value2: The upper bound of the range
Example of PL/SQL BETWEEN Operator
Step 1: Create Table
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
salary INT,
hire_date DATE,
department_id INT
);
CREATE TABLE Departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
Step 2: Insert Data
INSERT INTO Employees (employee_id, employee_name, salary, hire_date, department_id)
VALUES
(101, 'Alice Green', 45000, '2023-05-15', 1),
(102, 'John Doe', 25000, '2024-02-20', 2),
(103, 'Michael Brown', 60000, '2023-11-30', 1),
(104, 'Jane Smith', 50000, '2024-03-10', 3),
(105, 'Chris Johnson', 80000, '2023-08-05', 2);
INSERT INTO Departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');
Table UsesExample 1: Using BETWEEN with Numeric Values
Explanation : This query selects employee_id and salary from the Employees table where the salary is between 30,000 and 70,000 inclusive. Employees with salaries of 45,000, 60,000, and 50,000 fall within this range, so they are included in the output.
SELECT employee_id, salary
FROM Employees
WHERE salary BETWEEN 30000 AND 70000;
Output
Numeric ValueExplanation:
- This query returns employees whose salaries are outside the range of 30,000 to 70,000.
- Employees with salaries of 25,000 and 80,000 fall outside this range, so their details are included in the result.
- Salaries within the 30,000 to 70,000 range are excluded from the output.
Example 2: Using BETWEEN with Dates
Explanation : The query selects employee_id and employee_name from the Employees table where hire_date is between January 1, 2024, and June 30, 2024. Employees who were hired within this date range, such as John Doe and Jane Smith, are included in the output.
SELECT employee_id, employee_name
FROM Employees
WHERE hire_date BETWEEN '2024-01-01' AND '2024-06-30';
Output
Date RangeExplanation :
- The query selects employees who were hired between January 1, 2024, and June 30, 2024.
- Employees John Doe and Jane Smith were hired within this date range, so their details are included in the result.
- Employees hired outside this range are not included in the output.
Example 3: Using BETWEEN with the NOT Operator
Explanation : This query selects employee_id and salary from the Employees table where the salary is not between 30,000 and 70,000. Employees with salaries outside this range, such as 25,000 and 80,000, are returned in the output.
SELECT employee_id, salary
FROM Employees
WHERE salary NOT BETWEEN 30000 AND 70000;
Output
Not OperatorExplanation:
- The query returns employees whose salaries fall within the range of 30,000 to 70,000.
- Employees with salaries of 45,000, 60,000, and 50,000 meet this criterion and are included in the output.
- Salaries outside this range are not shown in the result set.
Conclusion
The BETWEEN operator is a versatile and straightforward tool in PL/SQL that helps filter records based on a range of values. Whether you’re working with numbers, dates, or even text, BETWEEN makes it easy to define and retrieve data within specific bounds. Combining BETWEEN with the NOT operator further extends its flexibility, allowing you to exclude ranges effortlessly. By understanding how to use this operator, you can write more efficient and readable SQL queries.
Similar Reads
SQL BETWEEN Operator
The BETWEEN operator in SQL is used to filter records within a specific range. Whether applied to numeric, text, or date columns it simplifies the process of retrieving data that falls within a particular boundary. In this article, we will explore the SQL BETWEEN operator with examples. SQL BETWEEN
3 min read
SQL | BETWEEN & IN Operator
In SQL, the BETWEEN and IN operators are widely used for filtering data based on specific criteria. The BETWEEN operator helps filter results within a specified range of values, such as numbers, dates, or text, while the IN operator filters results based on a specific list of values. Both operators
5 min read
PostgreSQL - BETWEEN Operator
The PostgreSQL BETWEEN operator is an essential tool for filtering data within a specific range. Often used in the WHERE clause of SELECT, INSERT, UPDATE, and DELETE statements, this operator simplifies range-based conditions, making queries faster and easier to read. In this article, we will explai
3 min read
PL/SQL IN Operator
The PL/SQL IN operator is a powerful tool used in SQL queries to check if a value matches any value in a list or a subquery result. It simplifies querying multiple values and can make your SQL code cleaner and more readable. The IN operator is typically used in the WHERE clause to filter results bas
6 min read
PL/SQL AND Operator
The PL/SQL AND operator is used to combine multiple conditions in a WHERE clause of an SQL query. It allows you to refine your query by ensuring that all specified conditions are met. AND queries which help in filtering data more precisely and can be crucial for retrieving accurate results from a da
7 min read
PL/SQL NOT Operator
PL/SQL, an extension of SQL in Oracle, offers various operators that allow us to perform logical operations on data. One such operator is the NOT operator, which is used to negate a condition, meaning it will return true if the condition is false and vice versa. The NOT operator is commonly used in
6 min read
PL/SQL EXISTS Operator
The EXISTS operator in PL/SQL is a powerful tool used to check the existence of records in a subquery. Unlike traditional comparison operators that evaluate data values, EXISTS focuses on whether a set of conditions returns any rows. It is commonly used to determine the presence or absence of record
6 min read
PL/SQL INTERSECT Operator
The PL/SQL INTERSECT operator is a powerful SQL set operation that allows us to return only the rows that are common to two or more SELECT queries. Unlike UNION or UNION ALL, which combine the results of different queries, INTERSECT focuses on finding the overlap between them. In this article, We wi
3 min read
PL/SQL Operators
The PL/SQL language offers various operators for data manipulation and logical processing. There are several types of these operators which include arithmetic operators, relational operators, comparison operators, and logical operators. In this guide, we will learn about the various PL/SQL operators
4 min read
PL/SQL NOT EQUAL Operator
In PL/SQL, the NOT EQUAL operator is used to compare two values and determine if they are not equal. If the values are different, the result of the comparison is true; otherwise, it is false. This operator is often used in conditional statements and queries to filter data based on inequality. In thi
6 min read