MariaDB Not Between [With 11 real exmples]

In this MariaDB tutorial, we will discuss how to use the MariaDB Not Between condition and look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Not Between
• MariaDB Not Between Dates
• MariaDB Not Between Enum
• MariaDB Not Between Foreign Key
• MariaDB Not Between Group By
• MariaDB Not Between Having
• MariaDB Not Between In
• MariaDB Not Between Join
• MariaDB Not Between Now
• MariaDB Not Between Order By
• MariaDB Not Between Or

MariaDB Not Between

This sub-topic will make us understand how to use the MariaDB NOT BETWEEN condition in the query and which is explained with the help of syntax and an example.

In MariaDB, the NOT BETWEEN condition is not the same as NOT( expression BETWEEN min and max). The HIGH_NOT_PRECEDENCE SQL_MODE setting has an impact on the definition of the alternative form NOT expr BETWEEN min AND max. Here is the syntax of the MariaDB NOT BETWEEN condition as given below:

SYNTAX:

SELECT expression FROM TABLENAME WHERE
NOT expression BETWEEN min AND max;

Before we move to the example of the NOT BETWEEN condition, we need to see the records of the SKULLCANDY table by the following query:

SELECT * FROM SKULLCANDY;

Here is an illustrated example of the MariaDB NOT BETWEEN condition by the following query:

EXAMPLE:

SELECT * FROM SKULLCANDY WHERE
NOT PRICE BETWEEN 50 AND 70;

As we see in the above query, we have retrieved all records from the SKULLCANDY table based on the WHERE condition.

If the PRICE column doesn’t match between 50 as minimum price and 70 as maximum price, then it will make the condition TRUE and will get the result set by using the SELECT statement.

Read: MariaDB GROUP BY with Example

MariaDB Not Between Dates

Let’s have a look at the example of the NOT BETWEEN condition for the DATES column in MariaDB.

First, let’s have a look at the records of the EMPLOYEE table by using the following query:

SELECT * FROM EMPLOYEE;

The MariaDB SELECT statement retrieves all records from the EMPLOYEE table.

SELECT * FROM employee
WHERE NOT `CURRENT_DATE` BETWEEN 2021-04-01 AND 2021-07-02
LIMIT 5;

• The WHERE condition on the CURRENT_DATE column is used to retrieve all records from the EMPLOYEE table, as seen in the accompanying query.
• If the current_date column does not fall between 2021-04-01 as the minimum date and 2021-07-02 as the maximum date, the NOT WHERE condition is used.
• Then the WHERE condition is TRUE, and the SELECT query is used to acquire the output result set.
• We used the LIMIT clause as LIMIT 5 after the query to extract all records from the EMPLOYEE table using the SELECT statement.
• Additionally, don’t forget to use the backslash single braces as ` ` in the CURRENT_DATE column, because this column is a reserved word key in the EMPLOYEE table.

Read: MariaDB Delete From Statement

MariaDB Not Between ENUM

Here we will understand how to use the MariaDB NOT BETWEEN condition with the ENUM data type in the query and which is explained with the help of a demonstrated example.

The ENUM is a string object in MariaDB that has a value selected from a list of permissible values defined at the time of column creation. The following are its benefits:

• Compact data storage. The ENUM uses numeric indexes like (1,2,3,… ) to represent index string values.
• Readable queries and input.

EXAMPLE:

SELECT * FROM states_of_usa
WHERE NOT STATE_SHORTFORM BETWEEN 'AK' AND 'GA'
LIMIT 5;

• We had retrieved all records from the STATES_OF_USA table using the WHERE criteria in the previous query.
• The WHERE condition returns TRUE only if the NOT BETWEEN condition returns TRUE when the SELECT query returns a value between Ak and GA in the STATES_OF_USA table.
• We used the LIMIT clause as LIMIT 5 after the WHERE condition to extract the first 5 records from the STATES_OF_USA table using the SELECT statement.

Read: How to load files into MariaDB

MariaDB Not Between Foreign Key

Here we’ll understand and learn how to use the MariaDB Not Between condition on the Foreign Key column in the table and which is explained with the help of an illustrated example.

In MariaDB, a foreign key is a column or collection of columns in one table that refers to a column or set of columns in another table, ensuring that the two tables’ informational system is secure.

A foreign key contained in the child table is a reference to the primary key contained in the parent table. Let’s have a look at the SKULLCANDY table having a foreign key column as the SKULLCANDY_ID column by the following query:

SELECT * FROM SKULLCANDY;

The MariaDB SELECT statement is used to retrieve all records from the SKULLCANDY table including the SKULLCANDY_ID column which is a foreign key constraint in the table.

Here is a sample example of the NOT BETWEEN operator on the SKULLCANDY_ID column of the SKULLCANDY table by using the below query:

EXAMPLE:

SELECT NAME,PRICE FROM SKULLCANDY
WHERE NOT SKULLCANDY_ID BETWEEN 6 AND 10;

• In the above query, we have retrieved the NAME and PRICE column from the SKULLCANDY table based on the WHERE condition.
• In the WHERE condition, by using the NOT BETWEEN condition on the SKULLCANDY_ID column to find the value which is not between 6 and 10.
• If the condition gets TRUE in the WHERE condition, it will retrieve the result of the NAME, PRICE column in the SKULLCANDY table by using the SELECT statement.

Read: MariaDB Rename Index

MariaDB Not Between Group By

In this section, we will use the MariaDB NOT BETWEEN condition with the GROUP BY clause and which is explained with the help of an illustrated example.

In a SELECT statement, the MariaDB GROUP BY clause is used to collect data from numerous records and group the results with one or more columns. The syntax of the NOT BETWEEN clause with the GROUP BY clause is given below:

SYNTAX:

SELECT expression FROM TABLE_NAME
WHERE NOT column_name BETWEEN MIN AND MAX 
GROUP BY COLUMN_NAME;

The syntax explanation:

• expression: The expression that is not included in an aggregate_function and must be included in the GROUP BY clause.
• TABLE_NAME: The table from which we need to retrieve records. And there should be at least one table in the FROM clause.
• WHERE: It is optional and there should be at least one table from which records to be selected.

Let’s a look at the NOT BETWEEN condition with the GROUP BY clause by the following query:

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE NOT STATE_SHORTFORM BETWEEN 'AZ' AND 'KL'
GROUP BY STATE_POPULTION;

In the preceding query, we have to retrieve all records from the STATES_OF_USA table by using the WHERE condition. In the WHERE condition, we have used the NOT BETWEEN condition to get the TRUE by not finding the value between AZ and KL in the STATE_SHORTFORM column.

And we can group them based on HIGH, MEDIUM, and LOW of the STATE_POPULATION column from the STATES_OF_USA table by using the GROUP BY clause in the SELECT statement. The condition will get TRUE, we will get the result set in the output.

Read: MariaDB Date_Format + Examples

MariaDB Not Between Having

Here we will understand how to use the MariaDB NOT BETWEEN condition with the HAVING clause in the query and which is explained with the help of an illustrated example.

The MariaDB HAVING clause is used in conjunction with the GROUP BY clause to limit the output rows to those that meet the condition that gets TRUE in the query. Let’s see the example of the MariaDB NOT BETWEEN condition with the HAVING clause in the query:

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE NOT STATE_SHORTFORM BETWEEN 'AZ' AND 'KL'
GROUP BY STATE_SHORTFORM
HAVING STATE_POPULATION 
LIMIT 5;

We have retrieved all records from the STATES_OF_USA table based on the WHERE clause, as shown in the above query. We used the NOT BETWEEN clause in the table to select entries whose names are not stated in the STATE_SHORTFORM column within AZ and KL in the WHERE condition.

Then we used the GROUP BY clause with the STATE_POPULATION column to group them by the STATE_SHORTFORM column, which is used with the HAVING clause. Only the GROUP BY clause, which is used on the STATE_SHORTFORM column in the STATES_OF_USA database, will make the HAVING clause condition TRUE.

At last, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records from the STATES_OF_USA table by using the SELECT statement.

Read: MariaDB COUNT Function

MariaDB Not Between In

Here we will understand how to use the MariaDB NOT BETWEEN condition with the IN clause in the same query and which is explained with the help of an illustrated example.

In MariaDB, the IN condition helps to reduce the use of the OR condition in the MariaDB SELECT, UPDATE, INSERT and DELETE statements. Let’s see the use of the NOT BETWEEN condition with the IN condition in a sample example by the following query:

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE NOT STATE_SHORTFORM BETWEEN 'AK' AND 'PK'
AND STATE_POPULATION IN ('MEDIUM','LOW');

• We have retrieved all records from the STATES_OF_USA table based on the WHERE clause, as shown in the above query.
• We used the NOT BETWEEN clause in the table to select entries whose names are not stated in the STATE_SHORTFORM column within AK and PK in the WHERE condition.
• So, these values from AK to PK will not come in the output from the STATE_SHORTFORM column by using the NOT BETWEEN condition in the SELECT statement.
• And by using the IN condition, we will retrieve the MEDIUM and LOW values from the STATE_SHORTFORM column in the output result.

Read: MariaDB Queries – Detailed Guide

MariaDB Not Between Join

Let’s understand how to use the MariaDB NOT BETWEEN condition with the JOIN condition for two tables in the query and which is explained with the help of a demonstrated example.

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause.

• The JOIN clause can be used when there are two or more two tables with common columns.

There are four types of JOIN in MariaDB:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

Here we are going to use the INNER JOIN clause with the NOT BETWEEN condition in the query which is explained in an example:

EXAMPLE:

SELECT SKULLCANDY_USA.SKULLCANDY_NAME,SKULLCANDY_USA.SKULLCANDY_USPRICE,SKULLCANDY.NAME
FROM SKULLCANDY_USA
INNER JOIN SKULLCANDY
ON SKULLCANDY_USA.SKULLCANDY_ID=SKULLCANDY.ID
WHERE NOT SKULLCANDY.PRICE BETWEEN 50.99 AND 100.99;

Using the INNER JOIN condition, we retrieve the SKULLCANDY_NAME and SKULLCANDY_USPRICE entries from the SKULLCANDY_USA table, as well as the NAME column from the SKULLCANDY table.

If the SKULLCANDY_ID and ID columns for both the SKULLCANDY_USA and SKULLCANDY tables met, the ON condition was true.

We’re utilizing the NOT BETWEEN condition based on the WHERE condition to fulfil the PRICE column of the SKULLCANDY table’s minimum and maximum values of 50.99 and 100.99, respectively.

If the WHERE condition matches the criteria, TRUE is returned, and the records for the result set are obtained using the SELECT statement from the SKULLCANDY_USA and SKULLCANDY tables.

Read: MariaDB Left Join – Helpful Guide

MariaDB Not Between Now

Here we are going to use the NOW function with the NOT BETWEEN condition in the query and which is explained with the help of an illustrated example.

In MariaDB, the NOW function returns the current date and time in the query. Here is the example of using the NOW function with the NOT BETWEEN condition by the following query:

EXAMPLE:

SELECT FIRST_NAME,NOW() FROM EMPLOYEE
WHERE NOT EMP_ID BETWEEN 5 AND 20;

• With the WHERE criteria in the previous query, we were able to extract all details from the EMPLOYEE table.
• We utilised the NOT BETWEEN condition in the WHERE condition to locate values that aren’t between 5 and 20.
• And the WHERE condition becomes TRUE if the NOT BETWEEN condition is TRUE.
• Then we will get all columns recorded in the resultset from the EMPLOYEE table by using the SELECT statement.

Read: MariaDB Select Statement

MariaDB Not Between Order By

Let’s discuss the MariaDB NOT BETWEEN condition with the ORDER BY clause in the query and which is explained with an example.

The MariaDB ORDER BY clause is used to arrange the records in ascending or descending order in the result set. Here’s an example of the NOT BETWEEN clause with the ORDER BY clause by the following query:

EXAMPLE:

SELECT STATE_NAME FROM STATES_OF_USA
WHERE NOT STATE_POPULATION BETWEEN 'HIGH' AND 'LOW'
ORDER BY STATE_NAME DESC;

Using the WHERE condition, we retrieve the STATE_NAME column from the STATES_OF_USA table in the preceding query.

We utilized the NOT BETWEEN condition in the WHERE condition to get the value from the STATE_POPULATION column that is not between the HIGH and LOW values.

If the WHERE condition is TRUE, we’ll use the ORDER BY clause on the STATE_NAME column, with the DESC keyword, to sort the result set in the descending order.

Read: MariaDB Set Variable

MariaDB Not Between Or

Let’s understand how to use the MariaDB NOT BETWEEN condition with the OR condition in the query and which is explained with the help of an illustrated example.

The OR condition in MariaDB is used to test two or more conditions, with records being returned if any of the conditions are met. The OR condition can be used in a SELECT, UPDATE, DELETE or INSERT query. Here is an illustrated example of the NOT BETWEEN condition with the OR condition by the following query:

EXAMPLE:

SELECT STATE_NAME,STATE_POPULATION FROM STATES_OF_USA
WHERE NOT STATE_POPULATION BETWEEN 'LOW' AND 'MEDIUM'
OR STATE_NAME LIKE 'A%';

The SELECT statement is used in the previous query to get records from the STATE_NAME and STATE_POPULATION columns of the STATES_OF_USA table. The NOT BETWEEN condition is then used in the WHERE condition to specify that the STATE_POPULATION value is not between LOW and MEDIUM.

Otherwise, we can use the prefix ‘%’ sign in the LIKE clause on the STATE_NAME column to employ the OR condition whose name starts with the alphabet A.

If the NOT BETWEEN and OR conditions are both TRUE, the SELECT statement will return a result with the values LOW, MEDIUM, and HIGH from the STATE_POPULATION column in the STATE_OF_USA table.

Also, take a look at some more MariaDB tutorials.

• MariaDB Window functions
• MariaDB Str_To_Date
• MariaDB Date Function
• MariaDB DATEDIFF Function

We addressed the MariaDB NOT BETWEEN condition in this MariaDB tutorial, as well as several sample instances connected to it. There are lists of the topic that comes under discussion:

• MariaDB Not Between
• MariaDB Not Between Dates
• MariaDB Not Between Enum
• MariaDB Not Between Foreign Key
• MariaDB Not Between Group By
• MariaDB Not Between Having
• MariaDB Not Between In
• MariaDB Not Between Join
• MariaDB Not Between Now
• MariaDB Not Between Order By
• MariaDB Not Between Or