What is the usage of in in mysql

In mysql, in is used with the where expression to query data within a certain range. The syntax is "select * from where field in (value)" or "select * from where field not in (value) ".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the usage of in in mysql

1. Basic usage

in in mysql is often used in where expressions , its function is to query data within a certain range.

select * from where field in (value1,value2,value3,…)

When IN is preceded by the NOT operator, it means the opposite meaning to IN, that is, not to select within these list items

select * from where field not in (value1,value2,value3,…)

2. IN subquery

Update In many cases, the value of the IN list item is ambiguous and 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 implemented the function of finding all users whose status is 0 (possibly are prohibited) all articles. First, get all users with status=0 through a query:

SELECT uid FROM user WHERE status=0

Then use the query result as a list item of IN to achieve the final query result. Note that the result returned in the subquery must be a field list item .

In the in subquery, we often encounter the problem of low query efficiency. The solution is as follows:

1. Still use the in subquery and query one more time

SELECT * FROM basic_zdjbxx WHERE suiji IN ( SELECT zdcode FROM ( SELECT zdcode FROM basic_h WHERE zdcode != "" ) AS h )

2. Use LEFT JOIN

SELECT zd.* FROM ( SELECT DISTINCT zdcode FROM basic_h WHERE zdcode != "" ) AS h LEFT JOIN basic_zdjbxx zd ON zd.suiji = h.zdcode

3. Supplementary instructions for IN operator

IN list items not only support numbers, but also characters and even time and date types, and can be These different types of data items are mixed and arranged without being consistent with the column type:

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

An IN can only perform range comparison on one field. If you want to specify more fields, you can use AND or OR logical operations. Operator:

  SELECT * FROM user WHERE uid IN(1,2) OR username IN('admin','manong')

After using AND or OR logical operators, IN can also be used with other operators such as LIKE, >=, =, etc.

4. Regarding the efficiency of the IN operator

If the list items of IN are certain, multiple ORs can be used instead:

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

// Equivalent is:

SELECT * FROM user WHERE (uid=2 OR aid=3 OR aid=5)

Generally believed:

1. If the index field is operated, the efficiency of using OR is higher than IN, but when the list items are uncertain (such as subquery is required to obtain the results ), you must use the IN operator. In addition, when the subquery table data is smaller than the main query, the IN operator is also applicable.

2. When in or or does not add an index to the field, the more fields are connected (1 or 2 or 3 or 4 or...), the query efficiency of or is much lower than that of in.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the usage of in in mysql. For more information, please follow other related articles on the PHP Chinese website!