mysql  in  operator

the MySQl in operator allows you to specify multiple values in a where clause.

mysql in Operator syntax

         select      column_list

         from        table_name
                                 
         where       (expression | column)    in    ('value1', 'value2', 'value3', ....);

the values in the list must be separated by a comma(,)

the in operator in MySQL can also be used in the where clause of other statement such as update,insert,delete etc.

note if the expression or any value in the list is null, the in operator is return null.


database table use in MySQL in operator example

below is a selection from the "user" table:

useridfirstnamelastnameemailcity
1sandeepkumarnehrasandeep76@gmail.comindia
2shyamdhakashyamkumar12@gmail.comjaipur
3ankitnehraankit11@gmail.comuae

mysql in operatro example

the following mysql statement selects all users with a city of 'india' or 'uae'

       
                   mysql> select   *        from     user

                   where       city     in  ('india','uae') ;

output :

useridfirstnamelastnameemailcity
1sandeepkumarnehrasandeep76@gmail.comindia
3ankitnehraankit11@gmail.comuae

note : you can combine the in operator with the not operator to determine if a value does not match any value in a list or a subquery.

       
                   select   *        from     user

                   where       city      not   in  ('uk','usa') ;

output : try it youself


note : you should never mix quoted and unquoted values in an in list because the comparison rules for quoted values(string) and unquoted values(number) differ.