How to use in query in mysql

In mysql, in is often used in where expressions to query data within a certain range. The syntax is "select*from where field in(value1,value2,..)". When in precedes When the not operator is added, it means that contrary to in, it does not select within these list items.

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

How to use in query in mysql

1. Basic usage

in is commonly used in mysql In the where expression, 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 selecting within these list items

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

2. IN subquery

In more 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 implement the method to find out all states All posts by 0 users (probably banned). 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)
// 等效为：
SELECT * FROM user WHERE (uid=2 OR aid=3 OR aid=5)

It is generally believed that:

1. If you are operating on index fields, using OR is more efficient than IN. However, when the list items are uncertain (such as subqueries are required to obtain results), you must use IN operations. symbol. In addition, when the subquery table data is smaller than the main query, the IN operator is also applicable.

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

Recommended learning: mysql video tutorial

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