How the IS-NULL operator works in Mariadb?

The MariaDB IS NULL operator is used to check if a value in a column or expression is NULL. It returns true if the value is NULL, and false if the value is not NULL. This operator is useful for identifying rows with missing or unknown values, as well as for handling null values in various data manipulations and calculations.

Syntax

The syntax for the MariaDB IS NULL operator is as follows:

expression IS NULL

• expression: This can be a column name, a subquery, or any valid expression that returns a value.

The IS NULL operator returns a boolean value (true or false) based on whether the expression evaluates to a null value or not.

Examples

Example 1: Check if a column value is NULL

This example demonstrates how to use the IS NULL operator to check if a column value is NULL.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50)
);

INSERT INTO employees (id, name, email) VALUES
    (1, 'John Doe', '[email protected]'),
    (2, 'Jane Smith', NULL);

SELECT name, email
FROM employees
WHERE email IS NULL;

The following is the output of this statement:

+------------+-------+
| name       | email |
+------------+-------+
| Jane Smith | NULL  |
+------------+-------+

In this example, the IS NULL operator filters out the rows where the email column is not NULL, returning only the row where email is NULL.

Example 2: Check if a subquery result is NULL

This example demonstrates how to use the IS NULL operator with a subquery.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

INSERT INTO orders (order_id, customer_id, order_date) VALUES
    (1, 101, '2023-03-01'),
    (2, 102, '2023-03-02'),
    (3, 101, '2023-03-03');

SELECT customer_id, order_date
FROM orders
WHERE (
    SELECT COUNT(*)
    FROM orders
    WHERE customer_id = orders.customer_id
) IS NULL;

The following is the output of this statement:

Empty set (0.01 sec)

In this example, the subquery calculates the count of orders for each customer. Since there are orders for all customers in the table, the subquery will never return NULL, and hence, the IS NULL operator will not match any rows.

Example 3: Check if multiple conditions are NULL

This example demonstrates how to use the IS NULL operator with multiple conditions.

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    phone VARCHAR(20)
);

INSERT INTO contacts (contact_id, first_name, last_name, email, phone) VALUES
    (1, 'John', 'Doe', '[email protected]', '123-456-7890'),
    (2, 'Jane', 'Smith', NULL, '987-654-3210'),
    (3, 'Bob', NULL, '[email protected]', NULL);

SELECT first_name, last_name, email, phone
FROM contacts
WHERE first_name IS NULL
    OR last_name IS NULL
    OR email IS NULL
    OR phone IS NULL;

The following is the output of this statement:

+------------+-----------+-----------------+--------------+
| first_name | last_name | email           | phone        |
+------------+-----------+-----------------+--------------+
| Jane       | Smith     | NULL            | 987-654-3210 |
| Bob        | NULL      | [email protected] | NULL         |
+------------+-----------+-----------------+--------------+

In this example, the IS NULL operator checks if any of the columns (first_name, last_name, email, or phone) are NULL. The result set includes rows where at least one column has a null value.

Example 4: Check if a value is NULL or a specific value

This example demonstrates how to use the IS NULL operator in combination with other conditions.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    status VARCHAR(20) DEFAULT 'PENDING'
);

INSERT INTO orders (order_id, customer_id, status) VALUES
    (1, 101, 'PENDING'),
    (2, 102, 'SHIPPED'),
    (3, 101, NULL);

SELECT order_id, customer_id, status
FROM orders
WHERE status IS NULL OR status = 'PENDING';

The following is the output of this statement:

+----------+-------------+---------+
| order_id | customer_id | status  |
+----------+-------------+---------+
|        1 |         101 | PENDING |
|        3 |         101 | NULL    |
+----------+-------------+---------+

In this example, the IS NULL operator checks if the status column is NULL, and the additional condition status = 'PENDING' includes rows where the status is ‘PENDING’. The result set includes rows where the status is either NULL or ‘PENDING’.

Related Functions

The following are a few functions related to the MariaDB IS NULL operator:

• MariaDB IS NOT NULL operator is used to check if a value in a column or expression is not NULL.
• MariaDB COALESCE() function is used to return the first non-null value from a list of expressions.
• MariaDB IFNULL() function is used to return the first non-null value from two expressions.
• MariaDB NULLIF() function is used to return NULL if two expressions are equal, and the first expression otherwise.

Conclusion

The MariaDB IS NULL operator is a valuable tool for identifying and handling null values in a database. It can be used in various scenarios, such as filtering data, checking for missing values, or handling null values in calculations. By combining the IS NULL operator with other conditions and functions, you can create powerful queries to handle null values effectively and ensure data integrity in your MariaDB database.