CODEWITHSUNDEEP
phpmysqlwhere-clause
Rrezarta Muja
Rrezarta Muja

Reputation: 55

What does the IN query search for?

How to search for rows where a varchar field contains some predefined integers?

ex. 

SELECT * FROM `map` WHERE `product` IN (2, 3, 4)

If all rows contain other integers, other than these three, the query returns an empty result.

Upvotes: 0

Views: 62

Answers (3)

TheOneWhoPrograms
TheOneWhoPrograms

Reputation: 659

SELECT * FROM `map` WHERE `product` IN (2, 3, 4)

The in is essentially a short hand way of checking if product is = to 2 or =3 or =4.

As Tjati stated, it can be rewritten as

SELECT * FROM `map` WHERE product = 2 OR product = 3 OR product = 4

It is typically good practice to do this to improve readability of your SQL.

A good link to practice or to better understand this concept is:

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_in

This is just a module off of the popular w3schools website:

http://www.w3schools.com/sql/sql_in.asp

A small example directly from mysql official page. (copied here incase links die)

Here you can see the full table.

mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | 170 | | 3 | Jack | 2007-04-06 | 100 | | 4 | Jill | 2007-04-06 | 220 | | 5 | Zara | 2007-06-06 | 300 | | 5 | Zara | 2007-02-06 | 350 | +------+------+------------+--------------------+ 7 rows in set (0.00 sec)

Now, suppose based on the above table you want to display records with daily_typing_pages equal to 250 and 220 and 170. This can be done using OR conditions as follows

mysql>SELECT * FROM employee_tbl ->WHERE daily_typing_pages= 250 OR
->daily_typing_pages= 220 OR daily_typing_pages= 170; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | 170 | | 4 | Jill | 2007-04-06 | 220 | +------+------+------------+--------------------+ 4 rows in set (0.02 sec)

Same can be achieved using IN clause as follows:

mysql> SELECT * FROM employee_tbl -> WHERE daily_typing_pages IN ( 250, 220, 170 );

+------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | 170 | | 4 | Jill | 2007-04-06 | 220 | +------+------+------------+--------------------+ 4 rows in set (0.02 sec)

Upvotes: 0

Ravi Sharma
Ravi Sharma

Reputation: 578

This is basically IN clause to replace many OR conditions

SELECT * FROM `map` WHERE `product` = 2 OR `product` = 3 OR `product` = 4

Upvotes: 0

tjati
tjati

Reputation: 6079

This is the same as

WHERE product = 2 OR product = 3 OR product = 4

Upvotes: 5

Related Questions