MySQL Operators And Comparison Functions

INTRODUCTION

In this tutorial, I am going to explain about working with Operators and Comparison Functions in MySQL with examples. This detailed article will cover the following topics as follows. Let’s see.

Introduction
Operators in MySQL
MySQL Comparison Functions
Conclusion

First, let's create a database with a few tables containing some dummy data. Here, I am providing the database with the tables containing the records, on which I am showing you the various examples. Let's see.

CREATE DATABASE MySQLComparsionFunctions;
USE MySQLComparsionFunctions;
CREATE TABLE Books(
BookID INT PRIMARY KEY auto_increment,
BookName varchar(100) NOT NULL,
BookType varchar(100) NOT NULL,
BookVendor varchar(100) NOT NULL,
BookDescription varchar(250),
BookInStock int NOT NULL
);
CREATE TABLE Library(
BookNumber INT NOT NULL,
BookCode VARCHAR(15) NOT NULL,
BookIssue INT NOT NULL,
CostEach DECIMAL(10, 2) NOT NULL,
PRIMARY KEY(BookCode)
);

By using the following query, let us check the following query.

SELECT * FROM mysqlcomparsionfunctions.books;

SELECT * FROM mysqlcomparsionfunctions.library;

Operators in MySQL

Operators may be used to build expressions. MySQL operators are very similar to mathematical operators.

Operators are classified into two categories, viz.

Binary
Unary

There are different types of operators in MySQL, let’s discuss them one by one.

Arithmetic operators
Boolean operators
Relational operators
Bitwise operators
Other operators
Unary operators

Note:

Binary operators work with two operands, but unary work with one.

To read more about MySQL Operators, Visit, Variables & Operators in MySQL.

MySQL Comparison Functions

Some of the MySQL Comparison Functions are GREATEST and LEAST, ISNULL. Let's discuss them, one by one.

GREATEST

Greatest function is used to find out the greatest value from two or more fields respectively.

Note

1) Greatest functions will return the "NULL" value if contains any "NULL" value in it.

2) If arguments contain the integer values and string values, then it compares them as numbers.

Syntax

GREATEST(value1, value2, value3, ...);

Example 1

SELECT GREATEST(8, 9, 10),
GREATEST(8, 9, 10, 100, 121, 151, 251),
GREATEST(555, null, 101);

Example 2

Returns “NULL” if any argument is “NULL”.

Syntax

SELECT GREATEST(2, NULL, 20, 251);

Example 3

SELECT BookName, BookType, GREATEST(BookInStock, 3000) AS max
FROM mysqlcomparsionfunctions.books;

Example 4

Using the Greatest function with user-defined variables.

SET @id1=1, @id2=2, @id3=3, @id4=4, @id5=5;
SELECT *
FROM mysqlcomparsionfunctions.books
WHERE BookID=GREATEST(@id1, @id2, @id3, @id4, @id5);

Example 5

SELECT GREATEST('B', 'A', 'C') AS GreatestValue,
GREATEST('A', 'ABC', 'AB') AS GreatestValue;

LEAST

Least Function is used to find out the smallest value from two or more fields respectively.

Note

1) Least Functions will return the "NULL" value if contains any "NULL" value in it.

2) If arguments contain the integer values and string values, then it compares them as numbers.

Syntax

LEAST(value1, value2, value3, ...);

Example 1

SELECT LEAST(8, 9, 10),
LEAST(8, 9, 10, 100, 121, 151, 251),
LEAST(555, null, 101);

Example 2

Returns “NULL” if any argument is “NULL”.

Syntax

SELECT LEAST(2, NULL, 20, 251);

Example 3

SELECT BookName, BookType, LEAST(BookInStock, 6000) AS MinimumStock
FROM mysqlcomparsionfunctions.books;

Example 4

Using Least function with user-defined variables.

SET @id1=1, @id2=2, @id3=3, @id4=4, @id5=5;
SELECT *
FROM mysqlcomparsionfunctions.books
WHERE BookID= LEAST(@id1, @id2, @id3, @id4, @id5);

Example 5

SELECT LEAST('B', 'A', 'C') AS LeastValue,
LEAST('VS', 'ABCD', 'ABC', 'AB') AS LeastValue;

ISNULL

In MYSQL, the ISNULL function is used in queries to test whether the argument is true or not. It takes one argument and returns 1 if the argument is NULL, or if the argument is not NULL, then it will return 0 values.

Syntax

ISNULL(expression);

Example

SELECT ISNULL(111),
ISNULL('Yes'),
ISNULL('Vatsa'),
ISNULL(1001 + NULL),
ISNULL(NULL);

Resources

Here are some useful resources:

CONCLUSION

In this article, I have discussed the concept of Operators and Comparison Functions in MySQL with various examples.

To read more about MySQL Functions, Visit, MySQL Functions.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.

Thanks for reading.