ORACLE BETWEEN Syntax
ORACLE
The Oracle BETWEEN condition is an SQL comparison operator.
Oracle BETWEEN
➔ The BETWEEN operator is used in the WHERE clause to select values within a range.
➔ The range includes both lower and upper bound values.
➔ The BETWEEN operator in expression
Syntax
expression BETWEEN value1 AND value2;
➔ expression: The name of the column whose value needs to be tested.
➔ value1: The lower value of the range.
➔ value2: The upper value of the range.
Example:
SELECT * FROM customers WHERE customer_id BETWEEN 400 AND 500;
➔ Use the BETWEEN condition together with the NOT operator to reverse the condition.
Example:
SELECT * FROM customers WHERE customer_id NOT BETWEEN 400 AND 500;
Multiple Conditions with AND
/* Multiple conditions in Where clause */
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE
salary BETWEEN 4500 AND 6500
AND department_id = 50;
Copy the code and try it out practically in your learning environment.
Multiple Conditions with OR
/* Multiple conditions in Where clause */
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE
salary BETWEEN 5500 AND 6500
OR department_id IN (50, 80, 90);
Copy the code and try it out practically in your learning environment.
Practice this lesson in your Oracle learning environment.
The best way to learn is to implement it yourself after any lesson.
Type or copy the code and try it out practically in your learning environment.
Create table:
Example
/* Drop table if the table name is already exists */
DROP TABLE IF EXISTS MYEMPLOYEES;
/* Create Table*/
CREATE TABLE MYEMPLOYEES (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
salary NUMBER(8, 2),
department_id NUMBER(4)
);
Copy the code and try it out practically in your learning environment.
Insert value:
Example
INSERT INTO MYEMPLOYEES (employee_id, first_name, last_name, salary, department_id)
VALUES (101, 'Neena', 'Kochhar', 17000, 90);
INSERT INTO MYEMPLOYEES (employee_id, first_name, last_name, salary, department_id)
VALUES (102, 'Lex', 'De Haan', 17000, 90);
INSERT INTO MYEMPLOYEES (employee_id, first_name, last_name, salary, department_id)
VALUES (103, 'Alexander', 'Hunold', 9000, 60);
Copy the code and try it out in your learning environment.
Check the query results against the data.
Example
SELECT * FROM MYEMPLOYEES WHERE employee_id BETWEEN 101 AND 103;
Copy the code and try it out practically in your learning environment.