How to use BETWEEN and IN the WHERE Statement of MySQL,

How to use BETWEEN and IN the WHERE Statement of MySQL,

MySQL BETWEEN usage
MySQL BETWEEN Syntax
The BETWEEN operator is used in the WHERE expression to select a data range BETWEEN two values. BETWEEN is used together with AND. The syntax is as follows:

WHERE column BETWEEN value1 AND value2WHERE column NOT BETWEEN value1 AND value2

Generally, value1 should be smaller than value2. When the NOT operator is added before BETWEEN, it indicates the opposite of BETWEEN, that is, a value out of this range is selected.
BETWEEN instance
Select User data with uid between 2 and 5:

SELECT * FROM user WHERE uid BETWEEN 2 AND 5

The returned query result is as follows:

In addition to the numeric type, BETWEEN also supports the string range. Select all users whose usernames are BETWEEN a and j (including a single letter k/K) as follows ):
SELECT * FROM user WHERE username BETWEEN 'a 'AND 'K'
The character range also supports Chinese characters, but it is generally meaningless.
MySQL BETWEEN boundary
Although almost all databases support the BETWEEN... AND operator, different databases have different processing methods for BETWEEN... AND. In MySQL, BETWEEN contains the value1 and value2 boundary values, as shown in the preceding example of user data with uid BETWEEN 2 and 5.
Some databases do not contain the value1 and value2 boundary values (similar to> and <), and some databases do not contain value1 (similar to> = and <). So when using BETWEEN... AND, check how your database handles the BETWEEN boundary value.
MySQL BETWEEN time and date
Between and is often used to retrieve content within a time or date segment. Below are some common examples of BETWEEN time AND date:

// In int timestamp format, query the data SELECT * FROM table WHERE column_time BETWEEN 1218196800 AND 1230739199 FROM 20:00:00 to. // DATE Format, query the data before 2008 to 2009 SELECT * FROM table WHERE column_time BETWEEN '2017-08-08 'AND '2017-01-01' // DATETIME format, query the data before 2008 20:00:00 to 2008 20:00:00 SELECT * FROM table WHERE column_time BETWEEN '2017-08-08 23:59:59 'AND '2017-12-31 '. data, we recommend that you use the> = Operator: // DATETIME format to query data FROM 2008 20:00:00 to the current time point in SELECT * FROM table WHERE column_time> = '2017-08-08 20:00:00'

It can be seen that the same requirement and different field types may be written differently. In terms of efficiency, the int timestamp format is the most efficient.
All the preceding BETWEEN examples are SELECT queries, but BETWEEN can also be used in SQL statements that use WHERE expressions such as UPDATE and DELETE.
MySQL BETWEEN data comparison
BETWEEN also has the data comparison function. The syntax is as follows:

expr BETWEEN min AND max

If 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. This function can be used to determine an expression or value. Otherwise, it is in a certain range:

// Return 0 SELECT 1 BETWEEN 2 AND 3 // return 1 SELECT 'B' BETWEEN 'A' AND 'C' // determine the date range: SELECT 20080808 BETWEEN 20080101 AND 20090101

BETWEEN and <, <=, >=,> and other operators have similar functions in some cases, but BETWEEN has a higher computing level and higher efficiency. Of course, due to the Boundary Value Problem of BETWEEN, it is not flexible enough. Therefore, in different situations, what operators should be used.

MySQL IN usage
MySQL IN syntax
The IN operator is used IN the WHERE expression to support multiple choices IN the form of list items. The syntax is as follows:

WHERE column IN (value1,value2,...)WHERE column NOT IN (value1,value2,...)

When the NOT operator is added before the IN operator, it indicates the opposite meaning of the IN operator, that is, it is NOT selected IN these list items.
IN instance
Select User data with uid 2, 3, and 5:

SELECT * FROM user WHERE uid IN (2,3,5)

The returned query result is as follows:

IN subquery
IN more cases, the value of the IN list item is not clear, but may be obtained through a subquery:
SELECT * FROM article WHERE uid IN (SELECT uid FROM user WHERE status = 0)
In this SQL example, we have found all articles for all users in the 0 State (which may be forbidden. First, obtain all users whose status is 0 through a query:

SELECT uid FROM user WHERE status=0

Then, use the query result as the IN list item to achieve the final query result. Note that the returned result IN the subquery must be a field list item.
IN operator supplementary description
IN list items not only support numbers, but also support characters or even time and date types. They can be arranged IN a mix of different types of data items without having to be consistent with the column type:

SELECT * FROM user WHERE uid IN(1,2,'3','c')

One IN can only compare the range of one field. To specify more fields, you can use the and or logical operator:
SELECT * FROM user WHERE uid IN (1, 2) OR username IN ('admin', '5idev ')
After the and or logical operators are used, IN can also be used with other operators such as LIKE,> =, AND =.
Efficiency of IN Operators
If the list items of the IN statement are definite, multiple OR statements can be used instead:

SELECT * FROM user WHERE uid IN (2, 3, 5) // equivalent: SELECT * FROM user WHERE (uid = 2 OR aid = 3 OR aid = 5)

It is generally considered that if you operate an index field, OR is more efficient than IN, but you must use the IN operator when you are not sure about the list items (if you need a subquery to obtain the result. IN addition, the IN operator is also applicable when the data IN the subquery table is smaller than that IN the primary query.

Articles you may be interested in:

• Usage of the BETWEEN clause in MySQL