MySQL IN usage

MySQL IN syntax

The IN operator is used in WHERE expressions to support multiple selections in the form of list items. The syntax is as follows:

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

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

IN Usage Example

Select user data with uid 2, 3, and 5:

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

Return query results as follows:

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 have implemented the method of detecting all articles of all users whose status is 0 (possibly 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 .

Supplementary instructions for IN operator

IN list items not only support numbers, but also characters and even time and date types, and these different types of data items can be mixed and arranged without having to match the column type. Be consistent:

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 operators:

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

Use AND or OR logic operator, IN can also be used with other operators such as LIKE, >=, =, etc.

About the efficiency of the IN operator

If the list items of IN are determined, 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 if it is To operate 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), the IN operator must be used. In addition, when the subquery table data is smaller than the main query, the IN operator is also applicable.

The above is the content of MySQL IN usage. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!