Comparison Operators

This MariaDB tutorial explores all of the comparison operators used to test for equality and inequality, as well as the more advanced operators.

Description

Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in MariaDB:

Comparison Operator	Description
=	Equal
<=>	Equal (Safe to compare NULL values)
<>	Not Equal
!=	Not Equal
>	Greater Than
>=	Greater Than or Equal
<	Less Than
<=	Less Than or Equal
IN ( )	Matches a value in a list
NOT	Negates a condition
BETWEEN	Within a range (inclusive)
IS NULL	NULL value
IS NOT NULL	Non-NULL value
LIKE	Pattern matching with % and _
EXISTS	Condition is met if subquery returns at least one row

Let's review the comparison operators that you can use in MariaDB.

Example - Equality Operator

In MariaDB, you can use the = operator to test for equality in a query. The = operator can only test equality with values that are not NULL.

For example:

SELECT *

FROM sites

WHERE site_name = 'Fastread.aitechtonic.com';

In this example, the SELECT statement above would return all rows from the sites table where the site_name is equal to "Fastread.aitechtonic.com".

Example - Equality Operator (Safe with NULL Values)

Because the = operator only compares non-NULL values, it is not safe to use with NULL values. To overcome this limitation, MariaDB added the <=> operator to allow you to test for equality with both NULL and non-NULL values.

To better explain the difference between the = operator and the <=> operator, we will include some examples with both queries and data.

Assuming that we have a table called sites in MariaDB that is populated with the following data:

site_id	site_name	server1	server2
1	Fastread.aitechtonic.com	MyServer	<NULL>
2	CheckYourMath.com	<NULL>	<NULL>
3	DigMinecraft.com	TBD	TDB
4	BigActivities.com	MyServer	Other

We could use the = operator in the following query:

SELECT *

FROM sites

WHERE server1 = server2;

Because we used the = operator, we would get the following results:

contact_id	last_name	website1	website2
3	DigMinecraft.com	TBD	TDB

In this example, the SELECT statement above would return all rows from the sites table where server1 is equal to server2. It does not return the second record where server1 and server2 are both NULL values.

Now let's see what happens when we rewrite our query using the <=> operator that is safe to use with NULL values:

SELECT *

FROM sites

WHERE server1 <=> server2;

Because we used the <=> operator, we would get the following results:

contact_id	last_name	website1	website2
2	CheckYourMath.com	<NULL>	<NULL>
3	DigMinecraft.com	TBD	TDB

Now our query returns all rows from the sites table where server1 is equal to server2, including those records where server1 and server2 are NULL values.

Example - Inequality Operator

In MariaDB, you can use the <> or != operators to test for inequality in a query.

For example, we could test for inequality using the <> operator, as follows:

SELECT *

FROM sites

WHERE site_name <> 'DigMinecraft.com';

In this example, the SELECT statement would return all rows from the sites table where the site_name is not equal to 'DigMinecraft.com'.

Or you could also write this query using the != operator, as follows:

SELECT *

FROM sites

WHERE site_name != 'DigMinecraft.com';

Both of these queries would return the same results.

Example - Greater Than Operator

You can use the > operator in MariaDB to test for an expression greater than.

SELECT *

FROM sites

WHERE site_id > 3;

In this example, the SELECT statement would return all rows from the sites table where the site_id is greater than 3. A site_id equal to 3 would not be included in the result set.

Example - Greater Than or Equal Operator

In MariaDB, you can use the >= operator to test for an expression greater than or equal to.

SELECT *

FROM sites

WHERE site_id >= 3;

In this example, the SELECT statement would return all rows from the sites table where the site_id is greater than or equal to 3. In this case, site_id equal to 3 would be included in the result set.

Example - Less Than Operator

You can use the < operator in MariaDB to test for an expression less than.

SELECT *

FROM sites

WHERE site_id < 50;

In this example, the SELECT statement would return all rows from the sites table where the site_id is less than 50. A site_id equal to 50 would not be included in the result set.

Example - Less Than or Equal Operator

In MariaDB, you can use the <= operator to test for an expression less than or equal to.

SELECT *

FROM sites

WHERE site_id <= 50;

In this example, the SELECT statement would return all rows from the sites table where the site_id is less than or equal to 50. In this case, site_id equal to 50 would be included in the result set.

Example - Advanced Operators

We've written specific tutorials to discuss each of the more advanced comparison operators in MariaDB. These topics will be covered later, or you can jump to one of these tutorials now.

• IN ( )
• NOT
• BETWEEN
• IS NULL
• IS NOT NULL
• LIKE
• EXISTS