A tutorial on using between and in in the where statement of MySQL

The between operator is used in the WHERE expression to select a range of data between two values. Between is used in conjunction with and with the following syntax:

WHERE column between value1 and value2
WHERE column not between value1 and value2

Usually value1 should be less than value2. When between is preceded by the NOT operator, it indicates the opposite meaning of between, which is to select a value outside the range.
Between instances
Select User Data with UID from 2 to 5:

SELECT * from user WHERE UID between 2 and 5

In addition to the numeric types, between also supports string ranges, selecting all username from A to J (and including single-letter k/k) as follows:
SELECT * FROM user WHERE username between ' a ' and ' K '
The character range also supports Chinese characters, but it usually doesn't make sense.
MySQL between border
Although almost all databases support between ... And operator, but different databases to between ... There is a difference in how and is handled. In MySQL, between contains value1 and value2 boundary values, such as the example above that selects user data with UID 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, check to see how your database handles between boundary values.
MySQL between Time and date
Between and is commonly used to retrieve content within a time or date period, here are some examples of common between time and date:

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

Date format, querying for data before 0 o'clock 2008-08-08 to 2009-01-01
SELECT * FROM table WHERE column_time between ' 2008-08-08 ' and ' 2009-01-01 '

DATETIME format, querying data from 2008-08-08 20:00:00 to 2009-01-01 0 o'clock
SELECT from table WHERE column_time between ' 2008-08-08 20:00:00 ' and ' 2008-12-31 23:59:59 '
However, for data that is queried to the current time, we recommend using the >= operator:
DATETIME format, query 2008-08-08 20:00:00 to the current moment of data
SELECT from table WHERE column_time >= ' 2008-08-08 20:00:00 '

It can be seen that the same requirements, different field types, may not be the same. In terms of efficiency, the int timestamp format is efficient.
Each of the above between examples, although all are SELECT queries, but between can also be used for UPDATE, DELETE, and other applicable WHERE expressions in SQL.
MySQL between data comparison
Between also has a data comparison function with the following syntax:

Expr between Min and Max

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

Returns 0
SELECT 1 between 2 and 3
Returns 1
SELECT ' B ' between ' a ' and ' C '
Judging the date range
SELECT 20080808 between 20080101 and 20090101

Between and <, <=, >=, > and other operators have similar functions in some cases, but the between is more computationally efficient. Of course, due to the existence of boundary value of between is not flexible enough, so different circumstances, the use of what operators, need to be treated specifically.

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

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

When in is preceded by the NOT operator, it means the opposite of in, that is, not selected within these list items.
In use instance
Select User Data with UID 2, 3, 5:

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

In sub-query
In more cases, the value of the in list item is ambiguous and may be obtained by a subquery:
SELECT * FROM article WHERE uid in (SELECT uid from user where status=0)
In this SQL example, we implemented all articles that identified all users with a status of 0 (which may be forbidden). First, all status=0 users are given a single query:

SELECT uid from user WHERE status=0

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

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

One 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 the user WHERE UID in (username) OR (' admin ', ' 5idev ')
After using the and or or logical operators, in can also be used with other operators such as like, >=, =, and so on.
About efficiency issues with the in operator
If the in list item is deterministic, then multiple OR can be used instead:

SELECT from user WHERE uid in (2,3,5)
The equivalent is:
SELECT from User WHERE (uid=2 or aid=3 or aid=5)

It is generally assumed that if you are working with an indexed field, using OR is more efficient than in, but you must use the in operator when the list item is indeterminate (if a subquery is required to get the result). Also, the in operator is used when the subquery table data is smaller than the primary query.

A tutorial on using between and in in the where statement of MySQL