{"id":193,"date":"2019-10-05T16:15:44","date_gmt":"2019-10-05T09:15:44","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=193"},"modified":"2020-04-11T23:24:10","modified_gmt":"2020-04-11T16:24:10","slug":"mariadb-in","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-in\/","title":{"rendered":"MariaDB In"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB in<\/code> operator to match a value with a list of values.<\/p>\n\n\n\n

Introduction to MariaDB in operator<\/h2>\n\n\n\n

The MariaDB in<\/code> is a logical operator that compares a value with a list of values:<\/p>\n\n\n

expression IN (v1, v2, v3, ...)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The in<\/code> operator returns true<\/code> if expression<\/code> matches any values in the list i.e., v1, v2, v3, etc.<\/p>\n\n\n\n
The value list can be a comma-separated list of literal values or a result set of another select<\/code> statement.<\/p>\n\n\n
expression in (select<\/span>-statement<\/span>)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The select<\/a><\/code> statement inside parentheses must return a list of values of one column, or the query will result in an error.<\/p>\n\n\n\n
Logically, the in<\/code> operator is equivalent to the following expressions:<\/p>\n\n\n
expression = v1 or \nexpression = v2 or \nexpression = v3 or\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Therefore, you can replace an expression with multiple or<\/code> operators with the in<\/code> operator to make the code cleaner.<\/p>\n\n\n\n
not in operator<\/h3>\n\n\n\n
The not<\/code> operator negates the in<\/code> operator:<\/p>\n\n\n
expression NOT IN (v1, v2, v3, ...)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It is syntactic sugar of the following expression:<\/p>\n\n\n
expression <> v1 AND\nexpression <> v2 AND \nexpression <> v3 AND\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MariaDB in operator examples<\/h2>\n\n\n\n
We’ll use the countries<\/code> table from the nation<\/code> sample database<\/a> to demonstrate the in<\/code> operator.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
A) Using the MariaDB in operator with a list of numeric values<\/h3>\n\n\n\n
The following example uses the in<\/code> operator to find countries whose region id is in a list of 1, 2, and 3:<\/p>\n\n\n
select<\/span>\n    name<\/span>,\n    region_id\nfrom<\/span> \n    countries\nwhere<\/span>\n    region_id in<\/span> (1<\/span>,2<\/span>,3<\/span>)\norder<\/span> by<\/span> \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
It is equivalent to the following query:<\/p>\n\n\n
select<\/span>\n    name<\/span>,\n    region_id\nfrom<\/span> \n    countries\nwhere<\/span>\n    region_id = 1<\/span> or<\/span> \n    region_id = 2<\/span> or<\/span> \n    region_id = 3<\/span>\norder<\/span> by<\/span> \n    name<\/span>; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
B) Using the MariaDB in operator with a select statement<\/h3>\n\n\n\n
The following statement returns a list of region id of regions in Asia<\/code>:<\/p>\n\n\n
select<\/span> \n    region_id \nfrom<\/span> \n    regions\nwhere<\/span> \n    name<\/span> like<\/span> '%Asia%'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
You can use the returned id list as the input of the in<\/code> operator as shown in the following query:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    region_id\nfrom<\/span> \n    countries\nwhere<\/span>\n    region_id in<\/span> (\n        select<\/span> \n            region_id\n        from<\/span> \n            regions\n        where<\/span> \n            name<\/span> like<\/span> '%Asia%'<\/span>\n\n)\norder<\/span> by<\/span> name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the MariaDB in<\/code> operator to match a value with a list of values.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The in<\/code> operator is often used in the where<\/a><\/code> clause of the select<\/code>, delete<\/code>, and update<\/code> statements.<\/p>\n\n\n\n