SQL BETWEEN condition with examples and syntax.
SQL BETWEEN Condition is using to retrieve values within the given range in a SELECT, INSERT, UPDATE, or DELETE statement.
syntax for BETWEEN Condition using in SQL:
expression BETWEEN value1 AND value2;
PARAMETERS OR ARGUMENTS
expression is a column or calculation.
value1 and value2 create an inclusive range that expression is compared to.
- The SQL BETWEEN Condition returns the records where expression is within the range from value1 to value2 (inclusive).
EXAMPLE - WITH NUMERIC
Below numeric example uses the BETWEEN condition to retrieve values within a numeric range.
SELECT * FROM employee WHERE age BETWEEN 22 AND 30;
This SQL BETWEEN example would return all rows where the age is between 22 and 30.
It is same as the following SQL SELECT statement:
SELECT * FROM employee WHERE age >= 22 AND age <= 30;
EXAMPLE - WITH DATE
You can use BETWEEN condition with Dates also. The below example uses the SQL BETWEEN condition to retrieve values within a date range.
SELECT * FROM employee WHERE Joining_date BETWEEN TO_DATE ('2010/01/01', 'yyyy/mm/dd') AND TO_DATE ('2012/12/31', 'yyyy/mm/dd');
EXAMPLE - USING NOT OPERATOR
The SQL BETWEEN condition can also be combined with the SQL NOT operator.
SELECT * FROM Employee WHERE age NOT BETWEEN 25 AND 30;
SQL BETWEEN condition example would return all rows where the age is NOT between 25 and 30.
It is same as following SQL SELECT statement:
SELECT * FROM Employee WHERE age < 25 OR age > 30;