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:
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:
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:
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:
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.