NOT IN

USAGE

check NOT IN (value[, ...]);
check NOT IN (SELECT ...);

Value Name

Description

check

A value expression

value

One or more value expressions separated by commas

SELECT ...

A SELECT that returns single-value rows

DETAILS

The NOT IN operator allows you to match an expression against multiple values, returning a truth value indicating if the check value does not match any 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 NOT IN (1, 2, 3) is easier to write than (var != 1 AND var != 2 AND var != 3).

The non-negated version is available as the IN operator.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

SELECT 42 NOT IN (1, 2, '42', 99) AS result;
+--------+
| result |
+--------+
|      0 |
+--------+
SELECT 9 NOT IN (1, 2, 3, 4) AS result;
+--------+
| result |
+--------+
|      1 |
+--------+
CREATE TABLE not_in_example (
  description VARCHAR(20),
  example INT
);
INSERT INTO not_in_example VALUES
  ('Everything', 42),
  ('Dalmations', 101),
  ('Agent', 99),
  ('B. Doz.', 13),
  ('CPU', 64);
SELECT *
FROM not_in_example
WHERE example NOT IN (42, 64, 99);
+-------------+---------+
| description | example |
+-------------+---------+
| Dalmations  |     101 |
| B. Doz.     |      13 |
+-------------+---------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES