Scaler
Academy
Data Science
Neovarsity
New
Skill Test
Free Masterclass
Search for Articles, Topics
MySQL Tutorial
Comparison Operators in Mysql

Comparison Operators in Mysql

By Ishika Mittal
6 mins read
Last updated: 16 May 2023
184 views
Learn via video courses
Topics Covered

Overview

In MySQL, comparison operators are used to compare values and return a boolean result, which can be either true or false. These operators are often used in conjunction with the WHERE clause of a SQL statement to filter rows based on specific criteria. Let's see further in this article about Comparison operators in MySQL.

Introduction

Comparison operators in MySQL are used to compare values and produce a boolean result that can be either true or false. The comparison operators are essential for performing conditional operations, such as filtering data or making decisions in programming.

For instance, when writing a MySQL query, comparison operators can be used in the WHERE clause and also in case to filter rows based on specific conditions. The result set will only include rows that meet the condition specified by the comparison operator.

Using comparison operators is straightforward, as they follow a standard syntax and operate on various data types.

In MySQL, there are several comparison operators, including:

  • Equal to (=): Returns true if the values on both sides are equal.
  • Not equal to (<> or !=): Returns true if the values on both sides are not equal.
  • Greater than (>): Returns true if the value on the left side is greater than the value on the right side.
  • Less than (<): Returns true if the value on the left side is less than the value on the right side.
  • Greater than or equal to (>=): Returns true if the value on the left side is greater than or equal to the value on the right side.
  • Less than or equal to (<=): Returns true if the value on the left side is less than or equal to the value on the right side.

Comparison operators in MySQL are frequently used in SQL statements to filter data based on specific conditions.

Equal To Operator (=)

The equal to the operator (=)is a Comparison operator in MySQL used to compare two values and returns true if they are equal. Syntax

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is equal to 25.

Example 2: Comparing two strings

In this example, the query will return all rows from the employees table where the first_name column is equal to John.

Example 3: Comparing with NULL value

In this example, the query will not return any rows, even if there are rows in the employees table where the department_id column is NULL. This is because the equal to the operator cannot be used to compare with the NULL value. Instead, we should use the IS NULL or IS NOT NULL operator to test for NULL values.

Not Equal To Operator (<> or !=)

The not equal to the operator (<> or !=) is a Comparison operator in MySQL used to compare two values and returns true if they are not equal. Syntax

or

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is not equal to 25.

Example 2: Comparing two strings

In this example, the query will return all rows from the employees table where the first_name column is not equal to John.

Example 3: Comparing with NULL value

In this example, the query will not return any rows, even if there are rows in the employees table where the department_id column is NULL. This is because the not equal to the operator cannot be used to compare with the NULL value. Instead, we should use the IS NULL or IS NOT NULL operator to test for NULL values.

Greater Than Operator (>)

The greater than operator (>) is a Comparison operator in MySQL used to compare two values and returns true if the value on the left side is greater than the value on the right side. Syntax

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is greater than 25.

Example 2: Comparing two dates

In this example, the query will return all rows from the orders table where the order_date column has a date greater than 2022-01-01.

Example 3: Comparing with NULL value

In this example, the query will not return any rows, even if there are rows in the employees table where the department_id column is NULL. This is because the greater than the operator cannot be compared with the NULL value. Instead, we should use the IS NULL or IS NOT NULL operator to test for NULL values.

Less Than Operator (<)

The less than operator (<) is a Comparison operator in MySQL used to compare two values and returns true if the value on the left side is less than the value on the right side. Syntax

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is less than 30.

Example 2: Comparing two dates

In this example, the query will return all rows from the orders table where the order_date column has a date less than 2022-01-01.

Example 3: Comparing with NULL value

In this example, the query will not return any rows, even if there are rows in the employees table where the department_id column is NULL. This is because the less than operator cannot be used to compare with the NULL value. Instead, we should use the IS NULL or IS NOT NULL operator to test for NULL values.

Greater Than or Equal To Operator (>=)

The greater than or equal to the operator (>=)is a Comparison operator in MySQL used to compare two values and returns true if the value on the left side is greater than or equal to the value on the right side. Syntax

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is greater than or equal to 25.

Example 2: Comparing two dates

In this example, the query will return all rows from the orders table where the order_date column is greater than or equal to 2022-01-01.

Example 3: Comparing with NULL value

In this example, the query will not return any rows, even if there are rows in the employees table where the department_id column is NULL. This is because the greater than or equal to the operator cannot be used to compare with the NULL value. Instead, we should use the IS NULL or IS NOT NULL operator to test for NULL values.

Less Than or Equal To Operator (<=)

The less than or equal to the operator (<=) is a Comparison operator in MySQL used to compare two values and returns true if the value on the left side is less than or equal to the value on the right side. Syntax

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is less than or equal to 35.

Example 2: Comparing two dates

In this example, the query will return all rows from the orders table where the order_date column has a date less than or equal to 2022-01-01.

Example 3: Comparing with NULL value

In this example, the query will not return any rows, even if there are rows in the employees table where the department_id column is NULL. This is because the less than or equal to the operator cannot be used to compare with the NULL value. Instead, we should use the IS NULL or IS NOT NULL operator to test for NULL values.

Null-Safe Equal To Operator (<=>)

The null-safe equal to the operator (<=>) is a Comparison operator in MySQL used to compare two values, taking into account any NULL values. It returns 1 if both values are equal or both values are NULL and returns 0 if they are not equal and at least one of them is NULL. Syntax

In the above syntax, value1 and value2 are the two values being compared.

Examples

Example 1: Comparing two integers

In this example, the query will return all rows from the employees table where the age column is either 25 or NULL.

Example 2: Comparing two dates

In this example, the query will return all rows from the orders table where the order_date column is either 2022-01-01 or NULL.

Example 3: Comparing with NULL value

In this example, the query will return all rows from the employees table where the department_id column is NULL.

Combining Operators

Comparison operators in MySQL can be combined to create more complex queries by using logical operators such as AND, OR, and NOT.

Here are some examples of combining comparison operators to create more complex queries in MySQL:

  1. Using the AND operator: The AND operator allows you to specify multiple conditions that must be true for a row to be returned. , For example,: If you want to retrieve all the customers who are from the USA and have spent more than $1000, you can use the following query:
  2. Using the OR operator: The OR operator allows you to specify multiple conditions where at least one must be true for a row to be returned. , For example,: If you want to retrieve all the customers who are either from the USA or have spent more than $1000, you can use the following query:
  3. Using the NOT operator: The NOT operator allows you to negate a condition. , For example,: If you want to retrieve all the customers who are not from the USA, you can use the following query:
  4. Using parentheses: You can use parentheses to group conditions together and control the order in which they are evaluated. , For example,: If you want to retrieve all the customers who are from the USA and have either spent more than $1000 or have a membership, you can use the following query:

Conclusion

  • Comparison operators in MySQL are used to compare values and return a boolean result.
  • The basic comparison operators in MySQL are =, <>, >, >=, <, and <=.
  • The LIKE operator is used for pattern matching, and the IN operator is used to compare a value against a set of values.
  • The BETWEEN operator is used to check if a value is within a range of values, and the IS NULL and IS NOT NULL operators are used to check for null values.
  • MySQL also supports logical operators such as AND, OR, and NOT, which can be used to combine multiple comparison expressions.
  • It is important to be aware of the data types of the values being compared, as MySQL will try to convert the values to a common type if necessary.

See Also

  • Arithmetic operators
  • Logical operators