How the NOT IN operator works in Mariadb?

The NOT IN operator is a logical operator that tests whether a value does not match any value in a list of values.

Posted on

The NOT IN operator is a logical operator that tests whether a value does not match any value in a list of values. It is equivalent to using the NOT operator with the IN operator. The NOT IN operator returns 1 (true) if the value is not in the list, and 0 (false) otherwise.

Syntax

The syntax of the NOT IN operator is as follows:

value NOT IN (value1, value2, ..., valueN)
  • value is the expression or column to be tested.
  • value1, value2, ..., valueN are the expressions or columns that define the list of values. They can be of any data type that can be compared, such as numeric, date, time, string, etc. The list can also be a subquery that returns a single column of values.

Examples

Example 1: Using NOT IN with numeric values

The following example uses the NOT IN operator to filter the rows from the products table where the price is not 5, 10, or 15.

SELECT id, name, price
FROM products
WHERE price NOT IN (5, 10, 15);

The output is:

+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | Laptop   |  1000 |
|  6 | Keyboard |    25 |
|  7 | Monitor  |   200 |
|  8 | Printer  |   150 |
|  9 | Scanner  |   120 |
+----+----------+-------+

Example 2: Using NOT IN with date values

The following example uses the NOT IN operator to filter the rows from the orders table where the order_date is not ‘2024-01-01’, ‘2024-01-15’, or ‘2024-01-31’.

SELECT id, customer_id, order_date, total_amount
FROM orders
WHERE order_date NOT IN ('2024-01-01', '2024-01-15', '2024-01-31');

The output is:

+----+-------------+------------+---------------+
| id | customer_id | order_date | total_amount  |
+----+-------------+------------+---------------+
|  1 |           1 | 2023-12-15 |       1500.00 |
|  2 |           1 | 2024-01-10 |        200.00 |
|  3 |           2 | 2024-01-20 |        300.00 |
|  4 |           2 | 2024-02-10 |        500.00 |
|  5 |           3 | 2024-02-15 |        300.00 |
|  6 |           4 | 2024-02-20 |        400.00 |
+----+-------------+------------+---------------+

Example 3: Using NOT IN with string values

The following example uses the NOT IN operator to filter the rows from the customers table where the last_name is not ‘Smith’, ‘Jones’, or ‘Brown’.

SELECT id, first_name, last_name, email
FROM customers
WHERE last_name NOT IN ('Smith', 'Jones', 'Brown');

The output is:

+----+------------+-----------+---------------------+
| id | first_name | last_name | email               |
+----+------------+-----------+---------------------+
|  1 | David      | Lee       | [email protected]   |
|  5 | Emma       | Watson    | [email protected] |
|  6 | Harry      | Potter    | [email protected]     |
+----+------------+-----------+---------------------+

Example 4: Using NOT IN with NULL values

The following example uses the NOT IN operator to filter the rows from the employees table where the salary is not 3000, 4000, or 5000, or is NULL.

SELECT id, name, department, salary
FROM employees
WHERE salary NOT IN (3000, 4000, 5000)
OR salary IS NULL;

The output is:

+----+---------+------------+--------+
| id | name    | department | salary |
+----+---------+------------+--------+
|  1 | John    | Sales      |   2000 |
|  2 | Jane    | Marketing  |   6000 |
|  4 | Mary    | HR         |   NULL |
|  6 | Patrick | IT         |   7000 |
+----+---------+------------+--------+

Note that the NOT IN operator returns NULL if any of the operands is NULL. Therefore, to include the rows with NULL values, we need to use the OR operator with the IS NULL operator.

Example 5: Using NOT IN with a subquery

The following example uses the NOT IN operator to filter the rows from the products table where the id is not in the list of product_ids from the order_details table.

SELECT id, name, price
FROM products
WHERE id NOT IN (SELECT product_id FROM order_details);

The output is:

+----+---------+-------+
| id | name    | price |
+----+---------+-------+
|  7 | Monitor |   200 |
|  8 | Printer |   150 |
|  9 | Scanner |   120 |
+----+---------+-------+

Some of the functions that are related to the NOT IN operator are:

  • IN: This is the opposite of the NOT IN operator. It tests whether a value matches any value in a list of values. It returns 1 (true) if the value is in the list, and 0 (false) otherwise.
  • NOT: This is a logical operator that negates the result of another operator or expression. It returns 1 (true) if the operand is 0 (false), and 0 (false) if the operand is 1 (true) or NULL. It can be used with the IN operator to achieve the same effect as the NOT IN operator.
  • BETWEEN: This is a logical operator that tests whether a value is within a specified range. It returns 1 (true) if the value is between the lower and upper bounds, and 0 (false) otherwise. It can be used to test for a continuous range of values, instead of multiple discrete values.

For example, the following query uses the BETWEEN operator to filter the rows from the products table where the price is between 10 and 20.

SELECT id, name, price
FROM products
WHERE price BETWEEN 10 AND 20;

The output is:

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  3 | Pen       |    10 |
|  4 | Notebook  |    15 |
|  5 | USB Drive |    15 |
+----+-----------+-------+

Conclusion

The NOT IN operator is a useful way to filter the data based on a list of values. It can be used with any data type that can be compared, such as numeric, date, time, string, etc. It is equivalent to using the NOT operator with the IN operator. It returns 1 (true) if the value is not in the list, and 0 (false) otherwise. To include the rows with NULL values, we need to use the OR operator with the IS NULL operator. Some of the related functions are IN, NOT, and BETWEEN.