MariaDB IN and NOT IN Operators

Objectives

Understanding of MariaDB IN and NOT IN Operators
MariaDB IN Operator in Where Condition
MariaDB NOT IN Operator in Where Condition

Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);

Understanding of MariaDB IN and NOT IN Operators

Assume that, you have to find out person whose incomes are 100, 150, 200 and 250. So how can you solve this problem? You may use OR, for example income = 100 OR income = 150 but this will be not efficient solution, because how many OR use can possible. Solve this problem efficiently you can use IN operator.

IN : Allow to specify multiple values in a WHERE clause like OR operator.
NOT IN : Allow to specify multiple values in a WHERE clause like OR operator, but NOT operator reverse the result.

MariaDB IN Operator in Where Condition

Problem: Find person whose incomes are 100, 150, 200 and 250

Syntax

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);

Example

SELECT * FROM person WHERE income IN (100, 150, 200, 250);

Output

+----+------------+-----------+------------------+------+--------+
| id | first_name | last_name | email            | age  | income |
+----+------------+-----------+------------------+------+--------+
|  2 | John       | Doe       | john@pf.local    |   19 |    100 |
|  3 | Tahsin     | NULL      | tahsin@pf.local  |   10 |    150 |
|  5 | Rakib      | Mia       | rakib@bf.local   |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local |   20 |    250 |
+----+------------+-----------+------------------+------+--------+
4 rows in set (0.345 sec)

MariaDB NOT IN Operator in Where Condition

Problem: Find person whose incomes are not 100, 150, 200 and 250