Detailed usage of IN in the MySQL Basic Tutorial

MySQL IN Syntax

The IN operator is used in the WHERE expression to support multiple selections as list items with the following syntax:

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

When IN is preceded by the NOT operator, it means the opposite of IN, that is, it is not selected within these list items.

IN usage example

Select uid user data of 2, 3, 5:

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

The results of the query are as follows:

IN Subquery

In more cases, the value of an IN list item is ambiguous and may be derived from a subquery:

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=0)

In this SQL example, we implemented the Find All Articles for all users with a status of 0 (which may be prohibited).All status=0 users are first retrieved from 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 description of the IN operator

IN list items support not only numbers but also characters and even time-date types, and they can be mixed and arranged without having to conform to column's type:

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

An IN can only compare ranges on one field. If you want to specify more fields, you can use the AND or OR logical operators:

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

With AND or OR logical operators, IN can also be used with others such as LIKE, > Operator 1 such as =, = is used together.

On the Efficiency of the IN Operator

If the list item for IN is deterministic, then multiple ORs can be used instead:

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

//Equivalent to:

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

1 It is generally accepted that OR is more efficient than IN for indexed fields, but the IN operator must be used when list items are uncertain (if subqueries are required to obtain results).The IN operator also applies when the subquery table data is smaller than the main query.

Thank you for reading, I hope to help you, thank you for your support on this site!