SQL BETWEEN

BETWEEN ... AND

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Original Table (used in the examples)

Example 1

To display the persons alphabetically between (and including) "Hansen" and exclusive "Pettersen", use the following SQL:

SELECT * FROM Persons WHERE LastName 
BETWEEN 'Hansen' AND 'Pettersen'

Result:

IMPORTANT! The BETWEEN...AND operator is treated differently in different databases. With some databases a person with the LastName of "Hansen" or "Pettersen" will not be listed (BETWEEN..AND only selects fields that are between and excluding the test values). With some databases a person with the last name of "Hansen" or "Pettersen" will be listed (BETWEEN..AND selects fields that are between and including the test values). With other databases a person with the last name of "Hansen" will be listed, but "Pettersen" will not be listed (BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value). Therefore: Check how your database treats the BETWEEN....AND operator!

Example 2

To display the persons outside the range used in the previous example, use the NOT operator:

SELECT * FROM Persons WHERE LastName 
NOT BETWEEN 'Hansen' AND 'Pettersen'

Result:

The Ektron Intranet lets you do everything you need to do on your corporate intranet and everything you want to do... all with just one application. What can you do with the Ektron Intranet? Navigate through content, documents, assets, colleagues and workgroups quickly and intuitively with enterprise search Communicate with friends and colleagues with forums, message boards and corporate blogging using the new Social Networking Platform Utilize the extensive out-of-the box features or customize your site through Ektron CMS400.NET's open architecture Promote collaboration in your organization through project workspaces where others can efficiently find information and work together Author/edit content, manage navigation, menus, audit trails, workflow and approvals with the best in breed Content Management See why there are 20,000+ Ektron integrations worldwide.
	TAKE THE VIDEO TOUR
	or download a FREE TRIAL today.

Jump to: Top of Page or HOME or Printer friendly page

W3Schools provides material for training only. We do not warrant the correctness of its contents. The risk from using it lies entirely with the user. While using this site, you agree to have read and accepted our terms of use and privacy policy.

Copyright 1999-2008 by Refsnes Data. All Rights Reserved.

W3Schools was converted to XHTML in December 1999