IN
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Operators for MariaDB Xpand
Topics on this page:
Overview
Presence of value in a list.
USAGE
check IN (value[, ...]);
check IN (SELECT ...);
Value Name | Description |
|---|---|
| A value expression |
| One or more value expressions separated by commas |
| A SELECT that returns single-value rows |
DETAILS
The IN operator allows you to match an expression against multiple values, returning a truth value indicating if the check value matches at least one of the list values.
The operator's arguments can consist of a literal list of values to match (separated by commas) or it can be a SELECT that fetches the list of values (one value per row).
In the first syntax, using var IN (1, 2, 3) is easier to write than (var == 1 OR var == 2 OR var == 3).
The second syntax is equivalent to using expression = ANY (SELECT ...)
The negated version is available as the NOT IN operator.
EXAMPLES
SELECT 42 IN (1, 2, '42', 99) AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT 9 IN (1, 2, 3, 4) AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
CREATE TABLE in_example (
description VARCHAR(20),
example INT
);
INSERT INTO in_example VALUES
('Everything', 42),
('Dalmations', 101),
('Agent', 99),
('B. Doz.', 13),
('CPU', 64);
SELECT *
FROM in_example
WHERE example IN (42, 64, 99);
+-------------+---------+
| description | example |
+-------------+---------+
| Everything | 42 |
| Agent | 99 |
| CPU | 64 |
+-------------+---------+
CREATE TABLE in_example_values (
val INT
);
INSERT INTO in_example_values VALUES
(13), (101);
SELECT *
FROM in_example
WHERE example IN (SELECT val FROM in_example_values);
+-------------+---------+
| description | example |
+-------------+---------+
| Dalmations | 101 |
| B. Doz. | 13 |
+-------------+---------+