MariaDB AND, OR and NOT Operators


Objectives

  • How to use AND Operator in MariaDB Where clause

  • How to use OR Operator in MariaDB Where clause

  • How to use NOT Operator in MariaDB Where clause

  • How to Combine AND, OR and NOT Operator in MariaDB Where clause


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)
)

--- 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', 'Mia', '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', 'Mia', 'hmtmcse.com@gmail.com', 30, 500);

Data set of the table

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  3 | Tahsin     | Mia       | tahsin@pf.local       |   10 |    150 |
|  4 | Jane       | Doe       | jane@gmail.com        |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  7 | Touhid     | Mia       | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+


How to use AND Operator in MariaDB Where clause

Let’s assume that you need data where age is Greater than 18 and income is Less than 300. So you can clearly understand that you must satisfy 2 conditions age and income for that you have to use AND operator.

Syntax

SELECT * FROM table_name WHERE column_name > 18 AND column_name < 300;

Example

SELECT * FROM person WHERE age > 18 AND income < 300;

Output

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


How to use OR Operator in MariaDB Where clause

Again assume that you need data where age is Greater than 18 or income is Less than 300. So you can clearly understand that you must satisfy any one condition age or income for that you have to use OR operator.

Syntax

SELECT * FROM table_name WHERE column_name > 18 OR column_name < 300;

Example

SELECT * FROM person WHERE age > 18 OR income < 300;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  3 | Tahsin     | Mia       | tahsin@pf.local       |   10 |    150 |
|  4 | Jane       | Doe       | jane@gmail.com        |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  7 | Touhid     | Mia       | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
6 rows in set (0.002 sec)


How to use NOT Operator in MariaDB Where clause

Now you have to select all data, but you can not select people whose last name is Doe. For this case you have to use NOT operator.

Syntax

SELECT * FROM table_name WHERE NOT column_name = 'Doe';

Example

SELECT * FROM person WHERE NOT last_name = 'Doe';

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
|  3 | Tahsin     | Mia       | tahsin@pf.local       |   10 |    150 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  7 | Touhid     | Mia       | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
5 rows in set (0.144 sec)


How to Combine AND, OR and NOT Operator in MariaDB Where clause

Now let’s combine AND, OR, NOT

Syntax

SELECT * FROM person WHERE NOT column_name = 'Doe' AND (column_name > 18 OR column_name < 300);

Example

SELECT * FROM person WHERE NOT last_name = 'Doe' AND (age > 18 OR income < 300);

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  3 | Tahsin     | Mia       | tahsin@pf.local       |   10 |    150 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  7 | Touhid     | Mia       | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
4 rows in set (0.001 sec)


MariaDB AND, OR and NOT Operators Video tutorial

Share