CoderZone.org

Category: >> Oracle PL/SQL >> BETWEEN Condition Bookmark and Share

<< lastnext >>

Snippet Name: BETWEEN Condition

Description: The BETWEEN condition allows you to retrieve values within a range.

Also see:
» LIKE Condition
» Combining the AND and OR Conditions
» OR Condition
» AND Condition
» WHERE Clause - Joins
» WHERE Clause - Conditions
» Having Clause

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 13th, 2009

-- The syntax for the BETWEEN condition is:
 
SELECT columns
FROM tables
WHERE column1 BETWEEN value1 AND value2;
 
-- This SQL statement will return the records where column1 
-- is within the range of value1 and value2 (inclusive). The 
-- BETWEEN function can be used in any valid SQL statement - 
-- select, insert, update, or delete.
 
 
 
-- Example #1 - Numbers
-- The following is an SQL statement that uses the BETWEEN 
-- function:
 
SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;
 
-- This would return all rows where the supplier_id is between 
-- 5000 and 5010, inclusive. It is equivalent to the following SQL 
-- statement:
 
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
 
 
 
-- Example #2 - Dates
-- You can also use the BETWEEN function with dates.
 
SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2003/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2003/12/31', 'yyyy/mm/dd');
 
-- This SQL statement would return all orders where the 
-- order_date is between Jan 1, 2003 and Dec 31, 2003 
-- (inclusive).
 
-- It would be equivalent to the following SQL statement:
 
SELECT *
FROM orders
WHERE order_date >= TO_DATE('2003/01/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2003/12/31','yyyy/mm/dd');
 
 
 
-- Example #3 - NOT BETWEEN
-- The BETWEEN function can also be combined with the NOT 
-- operator. For example:
 
SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;
 
-- This would be equivalent to the following SQL:
 
SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;
 
-- In this example, the result set would exclude all supplier_id 
-- values between the range of 5000 and 5500 (inclusive).
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 9