A tutorial on the use of between and in in Mysql's where statement _mysql

MySQL BETWEEN Usage
MySQL BETWEEN Syntax
The BETWEEN operator is used in a WHERE expression to select a range of data between two values. BETWEEN is used in conjunction with and syntax as follows:

Where column BETWEEN value1 and value2
where column not BETWEEN value1 and value2

Usually value1 should be less than value2. When the BETWEEN is preceded by the NOT operator, it means the opposite of BETWEEN, that is, the value outside the range is selected.
BETWEEN instance
Select User data with a UID between 2 and 5:

SELECT * from user WHERE uid BETWEEN 2 and 5

Return query results as follows:

In addition to numeric types, BETWEEN also supports a string range, which selects all username between A and J (and includes a single letter k/k):
Select * from user WHERE username BETWEEN ' a ' and ' K '
character ranges also support Chinese characters, but usually it doesn't make sense.
MySQL BETWEEN boundaries
Although almost all databases support BETWEEN ... And operator, but different databases on BETWEEN ... There is a difference between the and processing methods. In MySQL, BETWEEN contains value1 and value2 boundary values, such as the user data example above, where the UID is selected between 2 and 5.
While some databases do not contain value1 and value2 boundary values (similar to > and <), there are also databases that contain value1 and do not contain value2 (similar to >= and <). So in the use of BETWEEN ... And, please check how your database handles BETWEEN boundary values.
MySQL BETWEEN Time Date
BETWEEN and is often used to retrieve content within a time or date segment, following are some common examples of BETWEEN time dates:

int timestamp format, query 2008-08-08 20:00:00 to 2009-01-01 0 o'clock data
SELECT * from table WHERE column_time BETWEEN 1218196800 and 1230739199

//DATE format, query data from 2008-08-08 to 2009-01-01 0 o'clock
SELECT * FROM table WHERE column_time BETWEEN ' 2008-08-08 ' and ' 2009-01-01 '

/DATETIME format, query from 2008-08-08 20:00:00 to 2009-01-01 0 o'clock data
SELECT * from table WHERE Column_tim E BETWEEN ' 2008-08-08 20:00:00 ' and ' 2008-12-31 23:59:59 '
but for data that queries to the current time, it is recommended that you use the >= operator:
//DATETIME format, query 2008-08 -08 20:00:00 to the current moment data
SELECT * from table WHERE column_time >= ' 2008-08-08 20:00:00 '

As you can see, the same requirements, different field types, may not be the same. In terms of efficiency, the int timestamp format efficiency is optimal.
Each of the above BETWEEN examples, although all are SELECT queries, BETWEEN can also be used in SQL for the WHERE expression, such as UPDATE, DELETE, and so on.
MySQL BETWEEN Data Comparison
BETWEEN also has data comparison capabilities, as follows:

Expr BETWEEN min and Max

When the value of the expr expression is greater than or equal to min and is less than or equal to Max, the BETWEEN return value is 1, otherwise 0 is returned. With this function, you can judge an expression or value otherwise in an interval:

Back to 0
select 1 BETWEEN 2 and 3
//return 1
select ' B ' BETWEEN ' A ' and ' C '
/Judgment date range
SELECT 20080808 betw Een 20080101 and 20090101

BETWEEN and <, <=, >=, > and other operators have similar functions in some cases, but BETWEEN higher and more efficient. Of course, due to the existence of BETWEEN boundary value problem and inflexible, so the different conditions, the use of the operator, need to be treated concretely.

MySQL in usage
MySQL in syntax
The in operator is used in a WHERE expression to support multiple selections in the form of list items, as follows:

WHERE column in (value1,value2,...)
WHERE column not in (Value1,value2,...)

When in front plus the not operator, the expression is in the opposite meaning, that is, not selected within these list items.
In use instances
Select User Data with UID 2, 3, 5:

SELECT * from user WHERE uid in (2,3,5)

Return query results as follows:

In sub query
In more cases, the value of an in list item is ambiguous, and may be obtained by a subquery:
SELECT * from article where UID (select uid from user where status=0)
In this SQL example, we implement all articles that detect all users with a status of 0 (which may be prohibited). Start with a query to get all the Status=0 users:

SELECT uid from user WHERE status=0

The query result is then used as the list item in to implement the final query result, noting that the result returned in the subquery must be a field list item.
In operator supplemental description
In-list items support not only numbers, but also characters and even time-date types, and you can mix these different types of data items without having to be consistent with the type of column:

SELECT * from user WHERE uid in (1,2, ' 3 ', ' C ')

An in can only be scoped to a field, and if you want to specify more fields, you can use the and OR or logical operators:
SELECT * from user WHERE uid (1,2) OR username in (' admin ', ' 5idev ')
With and OR or logical operators, in can also be used with other operators such as like, >=, =, and so on.
About the efficiency problem with the in operator
If the list item in is OK, you can replace it with multiple OR:

SELECT * from user where UID (2,3,5)
//equivalent:
select * from user where (uid=2 or aid=3 or aid=5)

Generally, you must use the in operator if you are working on an indexed field and you use OR efficiency is higher than in, but when the list item is indeterminate (for example, if you want the subquery to get the result). In addition, the in operator is also applicable when the subquery table data is less than the primary query.