How the IS NOT NULL operator works in Mariadb?

The MariaDB IS NOT NULL operator is used to check if a value in a column or expression is not NULL. It returns true if the value is not NULL, and false if the value is NULL. This operator is useful for filtering out rows with null values or for ensuring that certain columns or expressions have non-null values.

Syntax

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

expression IS NOT NULL

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

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

Examples

Example 1: Check if a column value is not NULL

This example demonstrates how to use the IS NOT NULL operator to check if a column value is not 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 NOT NULL;

The following is the output of this statement:

+----------+----------------------+
| name     | email                |
+----------+----------------------+
| John Doe | [email protected] |
+----------+----------------------+

In this example, the IS NOT NULL operator filters out the row where the email column is NULL, returning only the row where email has a non-null value.

Example 2: Check if a subquery result is not NULL

This example demonstrates how to use the IS NOT 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 NOT NULL;

The following is the output of this statement:

+-------------+------------+
| customer_id | order_date |
+-------------+------------+
|         101 | 2023-03-01 |
|         102 | 2023-03-02 |
|         101 | 2023-03-03 |
+-------------+------------+

In this example, the subquery calculates the count of orders for each customer. The IS NOT NULL operator ensures that only rows where the subquery returns a non-null value (i.e., customers with at least one order) are included in the result set.

Example 3: Check if a calculated value is not NULL

This example demonstrates how to use the IS NOT NULL operator with a calculated expression.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2),
    discount DECIMAL(3, 2)
);

INSERT INTO products (product_id, price, discount) VALUES
    (1, 99.99, 0.10),
    (2, 49.99, NULL),
    (3, 24.99, 0.20);

SELECT product_id, price, discount,
    price * (1 - COALESCE(discount, 0)) AS discounted_price
FROM products
WHERE (price * (1 - COALESCE(discount, 0))) IS NOT NULL;

The following is the output of this statement:

+------------+-------+----------+------------------+
| product_id | price | discount | discounted_price |
+------------+-------+----------+------------------+
|          1 | 99.99 |     0.10 |          89.9910 |
|          2 | 49.99 |     NULL |          49.9900 |
|          3 | 24.99 |     0.20 |          19.9920 |
+------------+-------+----------+------------------+

In this example, the IS NOT NULL operator checks if the calculated discounted_price expression is not NULL. The COALESCE function is used to handle null values in the discount column, replacing them with 0. The result set includes only the rows where the calculated discounted_price is not NULL.

Example 4: Check if multiple conditions are not NULL

This example demonstrates how to use the IS NOT 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 NOT NULL
    AND last_name IS NOT NULL
    AND email IS NOT NULL
    AND phone IS NOT NULL;

The following is the output of this statement:

+------------+-----------+----------------------+--------------+
| first_name | last_name | email                | phone        |
+------------+-----------+----------------------+--------------+
| John       | Doe       | [email protected] | 123-456-7890 |
+------------+-----------+----------------------+--------------+

In this example, the IS NOT NULL operator checks if all the columns (first_name, last_name, email, and phone) are not NULL. Only the row where all columns have non-null values is included in the result set.

Example 5: Check if a value is not NULL or a specific value

This example demonstrates how to use the IS NOT 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 NOT NULL AND status != 'PENDING';

The following is the output of this statement:

+----------+-------------+---------+
| order_id | customer_id | status  |
+----------+-------------+---------+
|        2 |         102 | SHIPPED |
+----------+-------------+---------+

In this example, the IS NOT NULL operator checks if the status column is not NULL, and the additional condition status != 'PENDING' filters out rows where the status is ‘PENDING’. The result set includes only the row where the status is not NULL and not ‘PENDING’.

Related Functions

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

• MariaDB IS NULL operator is used to check if a value in a column or expression is 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 NOT NULL operator is a useful tool for filtering out rows with null values or ensuring that certain columns or expressions have non-null values. It can be used in various contexts, such as filtering data, checking for missing values, or validating data integrity. By combining the IS NOT NULL operator with other conditions and functions, you can create powerful queries to handle null values and ensure data consistency in your MariaDB database.