SQL BETWEEN Syntax and BETWEEN example tutorial

Source: Internet
Author: User
Tags numeric numeric value

SQL BETWEEN syntax and BETWEEN example tutorial
The BETWEEN operator is used in the WHERE clause to select a range of data between two values.

BETWEEN operator
Operator BETWEEN ... and selects a range of data between two values. These values can be numeric, literal, or date.

SQL BETWEEN Syntax
SELECT column_name (s)
From table_name
WHERE column_name
BETWEEN value1 and value2

Example 1: Display a record with ID 2-5 select * from Test where ID between 2 and 5 results: 234 records with ID Three are displayed, different database tutorials contain different ranges, and some databases display 5 records with ID four Example 2: A record with an ID of 2-5 is displayed select * from test where ID is not between 2 and 5 results: 16789 Records with ID Five can also be sorted alphabetically, example: Show records between title A to C-select * FR OM table_name where title between ' a ' and ' C '


The BETWEEN operator is used in the WHERE clause to select a range of data between two values.
BETWEEN operator
operator BETWEEN ... and selects a range of data between two values. These values can be numeric, literal, or date.

SQL BETWEEN Syntax
SELECT column_name (s)
From table_name
WHERE column_name
BETWEEN value1 and value2 the original table (used in the instance:)
Persons table:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue Www.111cn.netNew York
3 Carter Thomas Changan Street Beijing
4 Gates Bill Xuanwumen Beijing
BETWEEN operator Instance
To display people in alphabetical order between "Adams" (including) and "Carter" (excluding), use the following SQL:

SELECT * from Persons
WHERE LastName
BETWEEN ' Adams ' and ' Carter '
Result set:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York

Important: Different databases on BETWEEN ... There are differences in how the and operator is handled. Some databases list people between "Adams" and "Carter," but do not include "Adams" and "Carter"; some databases list people between "Adams" and "Carter" and include "Adams" and "Carter", while others The database lists people between "Adams" and "Carter", including "Adams", but does not include "Carter".

So, please check how your database handles BETWEEN .... And operator!
Instance 2
Use the NOT operator if you want to use the above example to show people outside the range:

SELECT * from Persons
WHERE LastName
Not BETWEEN ' Adams ' and ' Carter '
Result set:
Id LastName FirstName Address City
3 Carter Thomas Changan Street Beijing
4 Gates Bill Xuanwumen Beijing

BETWEEN ... And operator
Determines whether a person's numeric value is within a specific range, www.111cn.net this operator can only be used in SQL statements.
Expr[not]BETWEEN value1 and value2
Expr
Specifies the combination of fields and expressions to be evaluated.
Value1,value2
The range of values specified.
For example:
If you want to check out all employees aged 25-30 years from the staff form, you can use the following procedure.
SELECT name, age BETWEEN and 30
from staff form;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.