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;