MariaDB IS NULL Operator

In MariaDB, NULL represents an unknown value in a column. This is not the same as an empty or zero string. Instead, it means that the actual data value is not in the database. This is especially important when dealing with optional data fields or unknown information. In this article, we will understand how the IS NULL condition can be used to query data in MariaDB.

IS NULL Operator

The IS NULL condition is used to filter and retrieve rows where a certain column has NULL values.
The IS NULL operator returns true if the expression is null.

Syntax:

SELECT field1, field2,… FROM table_name WHERE field_name IS NULL;

Queries Using the IS NULL Operator in MariaDB

Create Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
Hire_date DATE
);

Insert Data

INSERT INTO employees VALUES
(1, 'John', 'Doe', 3, 50000.00,'2022-01-15'),
(2, 'Jane', 'Smith', 2, 60000.00,'2022-02-20'),
(3, 'Bob', 'Johnson', 1, 75000.00,'2022-03-10'),
(4, 'Vivek', 'Sharma', 2, 65000.00,NULL),
(5, 'Minal', NULL, 1, 70000.00,'2022-03-10');

Query 1: Finding Rows With Null Values

SELECT employee_id, first_name, last_name FROM employees WHERE last_name IS NULL;

This query returns the first name, last name, and a list of employees whose last name is not specified as zero.

Output:

IS NULL Operator

Query 2: With DELETE Operator

DELETE FROM employees WHERE first_name IS NULL;

This will delete all the records from the employees table where first name is null.

Output:

IS NULL Operator

Query 3: With ORDER BY Clause

SELECT last_name FROM employees ORDER BY ISNULL(last_name), last_name;

This query will retrieve last names from the employee table, and the sorted results are sorted so that rows with NULL last names appear first with no rows with no zero last names sorted increasing order

Output:

IS NULL Operator

Query 4: With ORDER BY Clause

SELECT worker_id, first_name FROM worker WHERE Hire_date IS NULL;

This query will give you the list of worker id and first names from worker table whose hire date is null.

Output:

IS NULL Operator

Conclusion

Effective database management in MariaDB requires an understanding of handling NULL values. The IS NULL condition allows you to control unknown data by allowing you to filter and retrieve information based on missing values. You can create flexible and extensible database structures by using NULL in your queries, regardless of whether you are working with customer, employee, or order information.

Article Tags :

Databases

MariaDB