MySQL Where in


MySQL IN syntax

The IN operator is used in WHERE expressions to support multiple selections in the form of 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, meaning that it is not selected within these list items.

Examples

Select user data whose uid is 2, 3, 5:

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

The results are as follows:

uid username password email regdate
2 John a193686a53e4de85ee3f2ff0576adf01 john@gmail.com 1278063917
3 Jack 0193686a35e4de85ee3f2ff0567adf490 jack@gmail.com 1278061380
5 admin a193686a53e4de85ee3f2ff0576adf01 admin@gmail.com 1291107029

subquery

In many cases, the value of the 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 implement all the articles that identify all users (possibly banned) with a status of 0. First of all through a query to get all the status = 0 of all users:

SELECT uid FROM user WHERE status = 0

And then the query results as a list of IN items to achieve the final query results, pay attention to the results returned in the subquery must be a field list item.


IN operator

IN list items not only support numbers, but also support the character and even the date and time types, and these different types of data items can be mixed without alignment with the type of column:

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

An IN can only range on a 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', 'developertut')

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




Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook