Skip to content

Oracle BETWEEN Operator 📏

Prerequisites: WHERE Clause

Mentor's Note: BETWEEN is the tool for finding ranges. It is inclusive, meaning it includes the starting and ending values you specify. 💡

🌟 The Scenario: The Budget Check 💰

  • Goal: Find all products that cost between ₹100 and ₹500.
  • Action: You use BETWEEN 100 AND 500. (This includes exactly ₹100 and ₹500!).

💻 1. Syntax & Examples

SELECT column_name FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example: Salary Range

SELECT first_name, salary 
FROM employees 
WHERE salary BETWEEN 5000 AND 10000;

Example: Date Range (Very Common!) 📅

SELECT first_name, hire_date 
FROM employees 
WHERE hire_date BETWEEN '01-JAN-2020' AND '31-DEC-2020';

🏗️ Architect's Note: Date Precision 🛡️

Be careful when using BETWEEN with Oracle DATE types that include Time. - '31-DEC-2020' is actually '31-DEC-2020 00:00:00'. - A record from 10 PM on that day will NOT be included! - Architect's Tip: Use TRUNC(hire_date) or explicit time comparisons for 100% accuracy.

📈 Learning Path