Mariadb Not Condition

MariaDB: NOT Condition

This MariaDB tutorial explains how to use the MariaDB NOT condition with syntax and examples.

Description

The MariaDB NOT condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the NOT condition in MariaDB is:

NOT condition

Parameters or Arguments

condition

The condition to negate.

Note

  • The MariaDB NOT condition requires that the opposite of the condition be must be met for the record to be included in the result set.

Example - Combine With IN condition

The MariaDB NOT condition can be combined with the IN condition.

Let's look at how to use the NOT condition with the IN condition in MariaDB.

For example:

SELECT *
FROM sites
WHERE site_name NOT IN ('AODBA.com', 'mySite.com');

This MariaDB NOT example would return all rows from the sites table where the site_name is not 'AODBA.com' or 'mySite.com'. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example - Combine With IS NULL condition

The MariaDB NOT condition can also be combined with the IS NULL condition.

Let's look at how to use the NOT condition with the IS NULL condition in MariaDB.

For example,

SELECT *
FROM sites
WHERE site_name IS NOT NULL;

This MariaDB NOT example would return all records from the sites table where the site_name does not contain a NULL value.

Example - Combine With LIKE condition

The MariaDB NOT condition can also be combined with the LIKE condition.

Let's look at how to use the NOT condition with the LIKE condition in MariaDB.

For example:

SELECT site_id, site_name
FROM sites
WHERE site_name NOT LIKE 'Big%';

By placing the MariaDB NOT Operator in front of the LIKE condition, you are able to retrieve all sites whose site_name does not start with 'Big'.

Example - Combine With BETWEEN condition

The MariaDB NOT condition can also be combined with the BETWEEN condition.

Let's look at how to use the NOT condition with the BETWEEN condition in MariaDB.

For example:

SELECT *
FROM sites
WHERE site_id NOT BETWEEN 500 AND 525;

This MariaDB NOT example would return all rows from the sites table where the site_id was NOT between 500 and 525, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM sites
WHERE site_id  500
OR site_id > 525;

Example - Combine With EXISTS condition

The MariaDB NOT condition can also be combined with the EXISTS condition.

Let's look at how to use the NOT condition with the EXISTS condition in MariaDB.

For example,

SELECT *
FROM sites
WHERE NOT EXISTS (SELECT * 
                  FROM pages
                  WHERE pages.site_id = sites.site_id);

This MariaDB NOT example would return all records from the sites table where there are no records in the pages table for the given site_id.