Be Sociable, Share!

Here’s another MySQL tutorial of how to use an array of values while comparing in WHERE clause instead of one single value or record.

While thinking of an algorithm for comparing more than one value in WHERE clause what we get in our mind is by using OR keyword. So the easy, efficient and the shortest way instead of applying many OR operators is the one, using IN operator. Before going with the syntax lets see some scenarios in which this operator may be quite useful.

Advertisement

Understanding With Examples:

Lets see some explicit examples

Having the same table in the database

first_name last_name age date_registered
 1  Bob  Samuel  23  2012-12-15 09:56:42
 2  Anna  Smith  21  2012-12-16 10:26:02
 3  Dan  Brown  19  2012-12-17 11:54:22
 4  Agatha  Christie  17  2012-12-18 20:56:02
 5  John  Watson  30  2012-12-19 14:46:43
 6  Daniel  Patinson  21  2012-12-20 10:56:02

 

Check A Value In An Array:

This query gives 1 if the given value exists somewhere in the array, 0 otherwise.

Syntax:

 SELECT 'value'
 IN (array)

Query:

SELECT ’Bob’IN (‘Daniel’,'Christie’,'Samuel’,'Brown’,'Bob’,'Dan’,'Smiht’,'Ethan’)

 Result:

mysql-tutorial-in-example2

 

Query:

SELECT ’Bob’IN (‘Daniel’,'Christie’,'Samuel’,'Brown’,'xyz’,'Dan’,'Smith’,'Ethan’)

 Result:

mysql-tutorial-in-example2

 

Replacement Of More Than One OR:

This query fetches all the records from matched values in the given array. Like instead of putting many OR operators we may use IN operator. This is also useful if we need to select many records based on one single array. This is how array can be operated inside MySQL.

Syntax:

 SELECT *
 FROM table
 WHERE column_1
 LIKE character-pattern

Query:

SELECT *
FROM employee_data
WHERE first_name
IN (‘Daniel’,'Christie’,'Samuel’,'Brown’,'Bob’,'Dan’,'Smith’,'Ethan’)

 Result:

mysql-in-tutorail-example1

 

Your comments will be acknowledged, if you got any question or want to share useful information.

Be Sociable, Share!