MariaDB In

Summary: in this tutorial, you will learn how to use the MariaDB in operator to match a value with a list of values.

Introduction to MariaDB in operator

The MariaDB in is a logical operator that compares a value with a list of values:

expression IN (v1, v2, v3, ...)
Code language: SQL (Structured Query Language) (sql)

The in operator returns true if expression matches any values in the list i.e., v1, v2, v3, etc.

The value list can be a comma-separated list of literal values or a result set of another select statement.

expression in (select-statement)
Code language: SQL (Structured Query Language) (sql)

The select statement inside parentheses must return a list of values of one column, or the query will result in an error.

Logically, the in operator is equivalent to the following expressions:

expression = v1 or 
expression = v2 or 
expression = v3 or
...
Code language: SQL (Structured Query Language) (sql)

Therefore, you can replace an expression with multiple or operators with the in operator to make the code cleaner.

not in operator

The not operator negates the in operator:

expression NOT IN (v1, v2, v3, ...)
Code language: SQL (Structured Query Language) (sql)

It is syntactic sugar of the following expression:

expression <> v1 AND
expression <> v2 AND 
expression <> v3 AND
...
Code language: SQL (Structured Query Language) (sql)

The in operator is often used in the where clause of the select, delete, and update statements.

MariaDB in operator examples

We’ll use the countries table from the nation sample database to demonstrate the in operator.

A) Using the MariaDB in operator with a list of numeric values

The following example uses the in operator to find countries whose region id is in a list of 1, 2, and 3:

select
    name,
    region_id
from 
    countries
where
    region_id in (1,2,3)
order by 
    name;
Code language: SQL (Structured Query Language) (sql)

It is equivalent to the following query:

select
    name,
    region_id
from 
    countries
where
    region_id = 1 or 
    region_id = 2 or 
    region_id = 3
order by 
    name; 
Code language: SQL (Structured Query Language) (sql)

B) Using the MariaDB in operator with a select statement

The following statement returns a list of region id of regions in Asia:

select 
    region_id 
from 
    regions
where 
    name like '%Asia%';
Code language: SQL (Structured Query Language) (sql)

You can use the returned id list as the input of the in operator as shown in the following query:

select 
    name, 
    region_id
from 
    countries
where
    region_id in (
        select 
            region_id
        from 
            regions
        where 
            name like '%Asia%'

)
order by name;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB in operator to match a value with a list of values.