Comparison Functions in MySQL

Introduction

Comparison functions in MySQL are built-in functions that allow us to compare values within a column or between multiple columns. These functions are useful for comparing columns with string, numeric, and datetime data types.

MySQL provides a variety of functions, including the simple greater-than and less-than operators, as well as advanced functions. In this article, we will discuss the following comparison functions in MySQL in detail:

• IFNULL() Function
• NULLIF() Function
• COALESCE() Function
• LIKE Operator
• REGEX Operator
• IN Operator
• BETWEEN Operator
• GREATEST() Function
• LEAST() Function

We will also see their practical implementation to get a better understanding of how these functions work and where to use them, depending on our use cases.

We will be using this sample "Employee" table in this article:

IFNULL Function

IFNULL is one of the comparison functions in MySQL that takes two expressions as arguments. If the value of the first expression is found to be NULL then it returns the second expression, otherwise, it returns the first expression.

SYNTAX of the Function:

Let us try to understand the same concept with the help of a use case. We've been given the Employee table having records of several employees.

Example:

Fetch the Name and CurrentPlace of the employees. In case their CurrentPlace is not available, we want their HomeTown to be displayed instead.

Query:

Output:

NULLIF Function

The NULLIF function takes two expressions and compares them. If the two expressions are equal then it returns NULL, otherwise it returns the first expression.

SYNTAX of the Function:

One of the most common use cases of the NULLIF function is to check columns containing NULL-like values (the values which look like NULL but are not, like empty strings).

Let us try to understand the same concept with the help of a use case:

Example:

Fetch all the employees having their HomeTown as NULL or "" (empty string).

Query:

Output:

Explanation:

The query first compares whether the employee's hometown is an empty string or not. If it is, then it returns null. If it's not, then it returns to the hometown of that employee. In this case, the WHERE clause will filter all employees that either have null or "" (empty string) as their hometown.

COALESCE Function

The COALESCE function returns the first non-null value from the given set of column(s).

In case all the passed columns have null values, then the function returns an alternate value as specified in the query.

SYNTAX of the Function:

If the value in the first column is found to be null then the function checks the second column and it goes on like that until a non-null value is found, or until all the specified columns are found to be null. Hence, the order of the columns passed into the function is very important.

Let's understand this function with the help of a use case.

Example:

Fetch the CurrentPlace of an employee. If the CurrentPlace is not present, then fetch his WorkPlace.

Query:

Output:

REGEXP Operator

REGEXP or Regular Expressions are used to find strings with some specific pattern or symbols according to the use case.

LIKE Operator

The LIKE operator is used with the WHERE clause for filtering. A LIKE operator uses Wildcards to match a column's value with a partial string.

Two wildcard symbols can be used with the LIKE operator.

1. %(Modulo): can be used as a placeholder for 0 or more unknown characters.
2. -(Underscore): can be used to represent one and only one unknown character.

We can also combine these two wildcards to filter data according to our needs.

SYNTAX of the Function:

Let us try to understand the same concept with the help of a use case:

Example:

Finding all employees whose WorkPlace starts with the character 'J' and ends with 'r'.

Query:

Output:

IN Operator

Whenever we have a list of values to filter from, we can use the IN operator and provide a list of values to compare against.

The IN operator checks and returns True if a value is present in the given list. It is an easier and more compact way of applying multiple OR operators in a single statement.

SYNTAX of the Function:

Let us try to understand the same concept with the help of a use case.

Example:

Fetch the details of all employees whose WorkPlace is either Lucknow or Varanasi.

Query:

Approach - 1: Using multiple OR operators

Approach - 2: Using the IN operator

Output:

BETWEEN Operator

The BETWEEN operator is used along with the AND keyword to check whether a value lies between a given range or not. It is important to note that both the lower and upper limit values are inclusive.

SYNTAX of the Function:

Let us try to understand the same concept with the help of a use case.

Example:

Fetch the name of all employees having a salary between 50000 to 75000.

Query:

Output:

GREATEST Function

The GREATEST() function is a comparison function that takes multiple arguments and returns the one with the largest value among them.

Syntax of the Function:

Let us try to understand the same concept with the help of an example.

Query:

Output:

LEAST Function

The LEAST() function is a comparison function that takes multiple arguments and returns the one with the smallest value among them.

Syntax of the Function:

Let us try to understand the same concept with the help of an example.

Query:

Output:

Using Multiple Comparison Functions in a Single Query

Till here, we've seen several comparison functions/operators. Now we are going to see how we can combine these functions/operators to make our queries more functional and efficient.

Let us try to understand the same concept with the help of use cases.

1. Combining LIKE and BETWEEN Operators

Example:

Fetch the details of employees whose HomeTown name starts with the character 'J' and who have a Salary between 50000 and 75000.

Query:

Output:

2. Combining REGEXP and IN Operators

Example:

Fetch records of all employees earning a six-digit Salary and having HomeTown either in Varanasi or Kolkata.

Query:

Output:

3. Combining IN and BETWEEN Operators

Example:

Fetch the records of employees who are earning between 40000 and 100000, having HomeTown either in Kolkata or Chennai.

Query:

Output:

These are just a few different scenarios where multiple comparison functions can be used within a single SELECT statement. You can come up with a bunch of combinations for fetching the required data, based on your use case.

See Also

• How to use Regex in SQL?
• Logical Operators in MySQL