The use of between with in and their differences in the where statement of MySQL

MySQL between Usage

1. Numerical type

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  and value2 WHERE column  not between  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 between 2  and 5

The results of the returned query are as follows:

2. String

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.
3.BETWEEN boundary
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.
4.BETWEEN Time 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:

// intTimestamp format, querying -- ,- ,  -:xx:xxTo the- on- ondata prior to 0 pipsSELECT *  from Table WHEREColumn_timebetween 1218196800  and 1230739199 //DATE format, query -- ,- ,To the- on- ondata prior to 0 pipsSELECT *  from Table WHEREColumn_timebetween '2008-08-08'  and '2009-01-01' // DATETIMEFormat, query -- ,- ,  -:xx:xxTo the- on- ondata prior to 0 pipsSELECT *  from Table WHEREColumn_timebetween '2008-08-08 20:00:00'  and '2008-12-31 23:59:59'

However, for data that is queried to the current time, it is recommended to use>=Operator:// DATETIMEFormat, query -- ,- ,  -:xx:xxdata to the current momentSELECT *  from Table WHEREColumn_time>= '2008-08-08 20:00:00'

It can be seen that the same requirements, different field types, may not be the same. The int timestamp format is the best 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.
5.BETWEEN Data Comparison
Between also has a data comparison function with the following syntax:

    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:

//Return0SELECT 1 between 2  and 3//Return1SELECT 'b' between 'a'  and 'C'//Judging the date rangeSELECT 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

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

WHERE column inch (value1,value2,...) WHERE column  not inch (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 inch (2,3,5)

The results of the returned query are as follows:

2.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  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 .
3.IN operator Supplemental Instructions
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 inch (1,2,'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.
4. Efficiency issues with the in operator
If the in list item is deterministic, then multiple OR can be used instead:

SELECT *  from User WHEREUidinch(2,3,5)//the equivalent is:SELECT *  from User WHERE(UID=2 ORAid=3 ORAid=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.

The difference between between and in

You can use between and in these two keywords if you want the result of the selection to be within a certain range. In most cases, the response is the same, but it's different if it's empty. As follows:

WHERE between  and pa_vb_e.        WHERE  in Pa_vb.

Their definition is as follows:

SELECT -  for Vbrk-Vbeln. parameters:pa_vb_s type Vbeln,                            pa_vb_e  type Vbeln.

If they are all empty, between will restrict the SELECT statement so that it cannot be selected for a single record, but in the opposite case, the restriction will not have any effect, and all records will be selected. This is the biggest difference between the two.

But there is a small trap, ranges variable and select-options feel on the same. But the performance will be different when using the SELECT statement. The code is as follows:

parameters:pa_vb_s type Vbeln, pa_vb_e type Vbeln. Ranges Pa_vb forVbrk-Vbeln. Pa_vb- Sign = 'I'. Pa_vb-option = 'BT'. Pa_vb-Low=pa_vb_s. Pa_vb-High=pa_vb_e. APPEND Pa_vb.

At this point, if you use the SELECT statement with in to limit, you will not be a choice! Here at first did not understand why this, tracking program found in append this sentence. If use selct-options this sentence, in the selection box does not fill in the words, then select-options this ragnes variable inside is empty value, is the inner table record number is 0! Equivalent to no append. But when we customize the Ragnes variable, we append the null value, even if there is no input. This is the same as the effect of between. It is possible to write the same thing as selct-options.

IF  is  not or  is  not INITIAL.           APPEND Pa_vb.       ENDIF.

The full test code is as follows:

For the differences between between and in, refer to: 4146114

The use of between with in and their differences in the where statement of MySQL