MySQL: Comparison Operators

This MySQL 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 MySQL:

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 MySQL.

Example - Equality Operator

In MySQL, 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 contacts

WHERE last_name = 'Johnson';

In this example, the SELECT statement above would return all rows from the contacts table where the last_name is equal to Johnson.

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, MySQL 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 contacts in MySQL that is populated with the following data:

contact_id

last_name

website1

website2

1

Johnson

techonthenet.com

<NULL>

2

Anderson

<NULL>

<NULL>

3

Smith

TBD

TDB

4

Jackson

checkyourmath.com

digminecraft.com

We could use the = operator in the following query:

SELECT *

FROM contacts

WHERE website1 = website2;

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

contact_id

last_name

website1

website2

3

Smith

TBD

TDB

In this example, the SELECT statement above would return all rows from the contacts table where the website1 is equal to website2. It does not return the second record where website1 and website2 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 contacts

WHERE website1 <=>website2;

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

contact_id

last_name

website1

website2

2

Anderson

<NULL>

<NULL>

3

Smith

TBD

TDB

Now our query returns all rows from the contacts table where website1 is equal to website2, including those records where website1 and website2 are NULL values.

Example - Inequality Operator

In MySQL, 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 contacts

WHERE last_name <> 'Johnson';

In this example, the SELECT statement would return all rows from the contacts table where the last_name is not equal to Johnson.

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

SELECT *

FROM contacts

WHERE last_name != 'Johnson';

Both of these queries would return the same results.

Example - Greater Than Operator

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

SELECT *

FROM contacts

WHERE contact_id > 50;

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

Example - Greater Than or Equal Operator

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

SELECT *

FROM contacts

WHERE contact_id >= 50;

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

Example - Less Than Operator

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

SELECT *

FROM inventory

WHERE product_id < 300;

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

Example - Less Than or Equal Operator

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

SELECT *

FROM inventory

WHERE product_id <= 300;

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

Example - Advanced Operators

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