Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: BETWEEN Condition Jump to:  
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)

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


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 306 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?