Usage of IN basic MySQL tutorial

Usage of IN basic MySQL tutorial

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:

Uid	Username	Password	Email	Regdate
2	James	A192136a53e4de85ee3f2ff0576adf01	Xiao@163.com	1278063917
3	Jack	0192136a35e4de85ee3f2ff0567adf490	Jack@gmail.com	1278061380
5	5 idev	A192136a53e4de85ee3f2ff0576adf01	5idev@5idev.com	1291107029

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','manong')

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.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!