MariaDB NULL and NOT NULL Condition in Where Clause


Objectives

  • Understanding of MariaDB Null Value

  • MariaDB NULL Value Check in Where Clause

  • MariaDB NOT NULL Value Check in 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)
)

--- 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 Null Value

In general NULL means no value. If you consider a registration from where

  • Firstname is required but

  • Lastname is not required. So Lastname may or may not provide by user.

If you want to resolve this problem then you need a field which will be empty or no value if user not provide, but if provide then will insert value.

You can solve this problem using NULL value column. In the table creation section you will learn more about NULL column.


MariaDB NULL Value Check in Where Clause

Find all the name whose last name has no value or NULL

Syntax

SELECT * FROM table_name WHERE column_name IS NULL;

Example

SELECT * FROM person WHERE last_name IS NULL;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  3 | Tahsin     | NULL      | tahsin@pf.local       |   10 |    150 |
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
2 rows in set (0.347 sec)


MariaDB NOT NULL Value Check in Where Clause

Find all the name whose last name has value or NOT NULL

Syntax

SELECT * FROM table_name WHERE column_name IS NOT NULL;

Example

SELECT * FROM person WHERE last_name IS NOT NULL;

Output

+----+------------+-----------+------------------+------+--------+
| id | first_name | last_name | email            | age  | income |
+----+------------+-----------+------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local  |    1 |   5000 |
|  2 | John       | Doe       | john@pf.local    |   19 |    100 |
|  4 | Jane       | Doe       | jane@gmail.com   |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local   |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local |   20 |    250 |
+----+------------+-----------+------------------+------+--------+
5 rows in set (0.000 sec)


MariaDB NULL and NOT NULL Value Video tutorial

Share