How to use MariaDB Not Equal Operator

In this MariaDB tutorial, we will look at how to use the MariaDB Not Equal operator and glance at several examples. There are lists of the topic that comes under discussion:

• MariaDB Not Equal
• MariaDB Not Equal Null
• MariaDB Not Equal Json Field
• MariaDB Not Equal Like
• MariaDB Not Equal Like Regex
• MariaDB Not Equal Primary Key
• MariaDB Where Not Equal
• MariaDB Not Equal Zero

MariaDB Not Equal

Here we will talk about and learn how to utilize the NOT EQUAL operator in MariaDB, which will be described using syntax and an example.

In MariaDB, the comparison operator is used in the WHERE condition to determine which records to be selected. Here are two signs that are used in the MariaDB for the NOT EQUAL operator <> and != sign.

It evaluates both MariaDB expressions and it will return 1 if they aren’t equal. And it will return 0 if they are equal. If either expression is NULL, then it will return a different data type which can be a number or string in the resultset.

First, let’s us see the syntax of the NOT EQUAL operator in the MariaDB by the following query:

SYNTAX:

SELECT expression FROM TABLE_NAME 
WHERE COLUMN_NAME [ <> | != ] [STRING | NUMBER];

Let’s see the example to use the inequality of the NOT EQUAL operator in the below query:

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE STATE_NAME !='ALASKA' 
LIMIT 5;

As we see in the above query, we have retrieved all records from the STATES_OF_USA table where the condition as the STATE_NAME column is not equal to ALASKA. This means that it will bring all records except the STATE_NAME column has ALASKA.

In the end, we have used the LIMIT clause as LIMIT 5 to bring the top 5 records from the STATES_OF_USA table by using the SELECT statement.

Read: MariaDB GROUP BY

MariaDB Not Equal Null

We will talk about and learn how to utilize the IS NOT NULL operator in MariaDB, which will be described using syntax and an example.

In MariaDB, the IS NOT NULL operator is used to check if the table carries NULL values in the query based on the WHERE condition and bring the result set. It is used in the INSERT, SELECT, UPDATE and DELETE statements.

Let’s see the syntax of the IS NOT NULL operator in the MariaDB by the following query:

SELECT expression FROM your_TABLENAME 
WHERE expression IS NOT NULL;

Let’s have a look at the illustrated example of the IS NOT NULL operator with the MariaDB SELECT statement by the following query:

EXAMPLE:

SELECT * FROM STATES_OF_USA 
WHERE STATE_POPULATION IS NOT NULL 
LIMIT 5;

In the previous query, we fetched all data from the STATES_OF_USA table using the WHERE condition to see if the STATE_POPULATION column in the table contains any NULL values. So, we utilized the LIMIT clause as LIMIT 5 to bring the top 5 records from the STATES_OF_USA table using the SELECT statement to get the result set.

Read: MariaDB COUNT Function

MariaDB Not Equal Json Field

We’ll study and comprehend how to use the NOT EQUAL operator with the JSON key in the query, which will be demonstrated with an example.

The JSON data type in MariaDB is a synonym for the LONGTEXT data type, and the JSON VALID function can be tested against the CHECK constraint to verify a valid JSON document is an input. Here is an example of a JSON key with a NOT EQUAL operator, as shown below:

EXAMPLE:

SELECT * FROM json_demo
WHERE USER_NAME !=' David_K';

In the preceding query, we have used the NOT EQUAL operator as != in the JSON_DEMO table by using the SELECT statement with the WHERE condition. In the condition it will check if the USER_NAME column doesn’t contain or is not equal to David_K, then the condition is TRUE and the query will be executed in the result set.

Read: MariaDB JSON Data Type

MariaDB Not Equal Like

We will understand and learn how to use the LIKE condition with the NOT EQUAL operator in MariaDB and which is explained with the help of an illustrated example.

In MariaDB, the LIKE condition is used for pattern matching. And it allows wildcard which is used in INSERT, SELECT, UPDATE, and DELETE statements. It is also case-sensitive. The illustrated example of the LIKE condition with the NOT EQUAL operator is given below:

EXAMPLE:

SELECT * FROM STATES_OF_USA WHERE STATE_NAME LIKE 'A%' 
AND STATE_SHORTFORM != 'AZ';

In the query, we retrieved all records from the STATES_OF_USA table by using the SELECT statement with the WHERE condition. In the WHERE condition, we have used the LIKE condition for pattern matching of alphabet A in the STATE_NAME column. And using the NOT EQUAL operator on the STATE_SHORTFORM column with string AZ.

In the LIKE condition, it uses the % sign for pattern matching for alphabet A in the STATE_NAME column. If both conditions get TRUE in the WHERE condition, it will bring the result from the STATES_OF_USA table by the SELECT statement.

Read: MariaDB Max Connections

MariaDB Not Equal Like Regex

In this section, we’ll learn how to use the like regex as an RLIKE condition with the NOT EQUAL operator in the query and which is explained with the help of an illustrated example.

In MariaDB, the RLIKE condition is used for pattern matchmaking in the WHERE clause of the SELECT, UPDATE, INSERT and DELETE statements. The syntax of the RLIKE pattern with NOT EQUAL operator is given below:

SYNTAX:

SELECT expression FROM TABLE_NAME
WHERE COLUMN_NAME RLIKE [PATTERN] AND
COLUMN_NAME [!= | <> ] [STRING | NUMBER];

The syntax explanation:

• expression: A character expressed as a column or field.
• pattern: The regular expression matchmaking. Here is the list of information for pattern matchmaking in the table:

VALUE	DESCRIPTION
^	Matches the first character of a string. It matches the start of a line wherever within the expression when used with a match_parameter of ‘m’.
$	The end of a string is matched. It matches the end of a line wherever within the expression when used with a match parameter of ‘m’.
*	There are zero or more times that this pattern matches.
+	One or more items are matched.
?	Matches single or multiple occurrences.
.	Apart from NULL, matches any text.
|	Used in the same way as a “OR” to denote multiple options.
[ ]	Used to provide a matching list in which any one of the letters in the list must match.
[^]	Used to define a nonmatching list in which any letter except those in the list is attempted to be matched.
( )	As a subexpression, it’s used to group words.
{m }	It matches M times
{m,}	It should match at least M times.
{m,n}	There are at least m, but no more than n matches.
\n	n is a number that ranges from 1 to 9. Before encountering \n, matches the nth subexpression contained within ().
[..]	One aggregation element, which can be more than one character, is matched.
[: :]	It matches character classes.
[==]	It matches equivalent classes.
\d	It matches the digit character in the RLIKE condition.
\D	It matches the non-digit characters in the RLIKE condition.
\w	It matches a word character
\W	It matches non-word characters.
\s	It matches the whitespace character.
\S	It matches the non-whitespace character.
*?	Has zero or more repetitions of the preceding pattern.
+?	Has one or more repetitions of the preceding pattern.
??	Has zero or one repetition of the preceding pattern.
{n}?	It matches the preceding pattern like N times.
{n,}?	It matches the preceding pattern at least N times.
{n,m}?	It should match the preceding pattern like N times but not more than M times.

NOTE:

• In MariaDB, the RLIKE condition is case sensitive match but when used in the binary string.

Let’s see the illustrated example of the RLIKE condition with the NOT EQUAL operator by the following query:

EXAMPLE:

SELECT * FROM STATES_OF_USA WHERE
STATE_NAME RLIKE '[A|E]' AND 
STATE_SHORTFORM !='RZ';

In the query, we retrieved all records from the STATES_OF_USA table by using the SELECT statement with the WHERE condition. In the WHERE condition, we have used the RLIKE condition for pattern matching of alphabet A or E in the STATE_NAME column. And using the NOT EQUAL operator on the STATE_SHORTFORM column with string RZ.

In the LIKE condition, it uses the [ ] and | sign for pattern matching for the alphabet A or E in the STATE_NAME column. If both conditions get TRUE in the WHERE condition, it will bring the result from the STATES_OF_USA table by the SELECT statement.

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

Read: MariaDB Reserved Words

MariaDB Not Equal Primary Key

We’ll understand and learn how to use the NOT EQUAL for the PRIMARY KEY column in the query and which is explained with the help of an illustrated example.

In MariaDB, the PRIMARY KEY column contains a non-null and non-zero value in the table. And there can be one primary key column in the table.

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE STATE_ID != 51
LIMIT 5;

• As we see in the preceding query, we retrieve all records from the STATES_OF_USA table with the WHERE condition.
• As the condition says that the STATE_ID column is not equal to 30 which means there are 50 records in the STATES_OF_USA table.
• And the STATE_ID column does not contain a value of 51 which means that the WHERE condition is TRUE.
• In the result set, we have used the LIMIT clause as LIMIT 5 to bring the top 5 records from the STATES_OF_USA table by using the SELECT statement.

Read: MariaDB Reset Root Password

MariaDB Not Equal Zero

In this section, we will know how to use the NOT EQUAL operator with the number ZERO in the query and which is explained with the help of syntax and illustrated example.

In the MariaDB query, we are going to use the WHERE condition with the NON EQUAL operator and give the operator a value of zero. Now let’s see the example by the following query:

EXAMPLE:

SELECT * FROM CUSTOMER_LIST 
WHERE PRICE !=0
LIMIT 10;

In the preceding query, we retrieved all records from the CUSTOMER_LIST table with the WHERE condition. As the condition says, when the PRICE column is not equal to zero, it will all bring all records from the CUSTOMER_LIST table except the records which contain zero value in the PRICE column of the table.

In the end, we have used the LIMIT clause as LIMIT 5 to bring the top 10 records from the CUSTOMER_LIST table by using the SELECT statement.

Read: MariaDB Drop Table + Examples

MariaDB Where Not Equal

In this section, we will learn how to use the WHERE condition with the NON EQUAL operator as zero value in the query and which is explained with the help of syntax and illustrated example.

In MariaDB, the WHERE clause is used in various statements like SELECT, INSERT, DELETE and UPDATE. It is used to retrieve records based on the condition if it gets TRUE. Let’s see the illustrated example of the NOT EQUAL operator after the WHERE condition is given below:

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE STATE_POPULATION !='HIGH'
LIMIT 5; 

As we see in the preceding query, we have retrieved all records from the STATES_OF_USA table based on the WHERE condition. As the condition says, it will retrieve the record only when the STATE_POPULATION column is not equal to HIGH in the STATES_OF_USA table.

In the result set, we have used the LIMIT clause as LIMIT 5 to give the top 5 records from the STATES_OF_USA table by using the SELECT statement.

You may also like to read the following MariaDB tutorials.

• MariaDB Left Join – Helpful Guide
• MariaDB Alter Table If Exists
• MariaDB Insert If Not Exists
• MariaDB Logs – Helpful Guide
• MariaDB Drop Index + Examples
• MariaDB Enable Remote Access
• MariaDB Foreign Key + Examples

In this MariaDB tutorial, we understood how to use the MariaDB NOT EQUAL operator and also look at some examples. There are lists of the topic that comes under discussion:

• MariaDB Not Equal
• MariaDB Not Equal Null
• MariaDB Not Equal Json Field
• MariaDB Not Equal Like
• MariaDB Not Equal Like Regex
• MariaDB Not Equal Primary Key
• MariaDB Where Not Equal
• MariaDB Not Equal Zero