PL/SQL BETWEEN Operator

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');

Example 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

Explanation:

• 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

Explanation :

• 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

Explanation:

• 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.