How the IN() operator works in Mariadb?

The MariaDB IN operator is used to determine if a specified value matches any value in a list or a subquery. It is often used in the WHERE clause to filter the results of a SELECT, UPDATE, or DELETE statement.

Syntax

The syntax for using the IN operator in MariaDB is as follows:

value IN (value1, value2, ..., valueN)

• value: The value to compare against the list.
• value1, value2, ..., valueN: A list of values to compare to value.

Examples

Example 1: Simple List

This example demonstrates how to use the IN operator with a simple list of values.

SELECT 'apple' IN ('banana', 'apple', 'orange') AS Result;

Output:

+--------+
| Result |
+--------+
|      1 |
+--------+

The output is 1 (true) because ‘apple’ is in the list.

Example 2: Numeric Comparison

This example shows the IN operator used with numeric values.

SELECT 42 IN (38, 39, 40, 41, 42) AS Result;

Output:

+--------+
| Result |
+--------+
|      1 |
+--------+

The number 42 is in the list, so the result is 1.

Example 3: Column Data

This example uses the IN operator to filter results from a table.

DROP TABLE IF EXISTS fruits;
CREATE TABLE fruits (
    id INT,
    name VARCHAR(50)
);
INSERT INTO fruits (id, name) VALUES (1, 'apple'), (2, 'banana'), (3, 'orange');

SELECT * FROM fruits WHERE name IN ('apple', 'orange');

Output:

+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    3 | orange |
+------+--------+

The query returns rows where the fruit name is either ‘apple’ or ‘orange’.

Example 4: Subquery

This example demonstrates the IN operator with a subquery.

SELECT * FROM fruits WHERE id IN (SELECT id FROM fruits WHERE name = 'apple');

Output:

+------+-------+
| id   | name  |
+------+-------+
|    1 | apple |
+------+-------+

The subquery finds the id of ‘apple’, and the main query returns the row with that id.

Example 5: Using NOT IN

This example shows how to use NOT IN to exclude values.

SELECT * FROM fruits WHERE name NOT IN ('banana');

Output:

+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    3 | orange |
+------+--------+

The query returns rows where the fruit name is not ‘banana’.

Related Functions

Here are a few functions related to the MariaDB IN operator:

• MariaDB EXISTS operator checks for the existence of rows returned by a subquery.
• MariaDB ANY and ALL operators compare a value to each value in a list or subquery.
• MariaDB FIND_IN_SET() function searches for a value within a comma-separated list of values.

Conclusion

The IN operator in MariaDB is a powerful tool for filtering data based on a list of values or the results of a subquery. It simplifies queries that would otherwise require multiple OR conditions and enhances readability and maintainability of SQL code. When used appropriately, it can make data retrieval operations more efficient and intuitive. Remember to use IN judiciously, as using it with large lists or subqueries can impact performance. Always test and optimize your queries to ensure they meet your application’s performance requirements.