Post

MySQL Operators And Comparison Functions

• 16.3k
• 0
• 7

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.
1. Introduction
2. Operators in MySQL
3. MySQL Comparison Functions
4. 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.
1. CREATE DATABASE MySQLComparsionFunctions;
2.
3. USE MySQLComparsionFunctions;
4. CREATE TABLE Books(
5.   BookID INT PRIMARY KEY auto_increment,
6.   BookName varchar(100) NOT NULL,
7.   BookType varchar(100) NOT NULL,
8.   BookVendor varchar(100) NOT NULL,
9.   BookDescription varchar(250),
10.   BookInStock int NOT NULL
11. );
12.
13. CREATE TABLE Library(
14.   BookNumber INT NOT NULL,
15.   BookCode VARCHAR(15) NOT NULL,
16.   BookIssue INT NOT NULL,
17.   CostEach DECIMAL(10, 2) NOT NULL,
18.   PRIMARY KEY(BookCode)
19. );
By using the following query, let us check the following query.

1)
1. SELECT * FROM mysqlcomparsionfunctions.books;
2)
1. 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.
1. Binary
2. 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
1. SELECT GREATEST(8, 9, 10),
2. GREATEST(8, 9, 10, 100, 121, 151, 251),
3. GREATEST(555, null, 101);

Example 2

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

Syntax
1. SELECT GREATEST(2, NULL, 20, 251);

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

Example 4

Using the Greatest function with user-defined variables.
1. SET @id1=1, @id2=2, @id3=3, @id4=4, @id5=5;
2. SELECT *
3. FROM mysqlcomparsionfunctions.books
4. WHERE BookID=GREATEST(@id1, @id2, @id3, @id4, @id5);

Example 5
1. SELECT GREATEST('B''A''C'AS GreatestValue,
2.   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
1. SELECT LEAST(8, 9, 10),
2. LEAST(8, 9, 10, 100, 121, 151, 251),
3. LEAST(555, null, 101);

Example 2

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

Syntax
1. SELECT LEAST(2, NULL, 20, 251);

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

Example 4

Using Least function with user-defined variables.
1. SET @id1=1, @id2=2, @id3=3, @id4=4, @id5=5;
2. SELECT *
3. FROM mysqlcomparsionfunctions.books
4. WHERE BookID= LEAST(@id1, @id2, @id3, @id4, @id5);

Example 5
1. SELECT LEAST('B''A''C'AS LeastValue,
2.   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
1. SELECT ISNULL(111),
2.   ISNULL('Yes'),
3.   ISNULL('Vatsa'),
4.   ISNULL(1001 + NULL),
5.   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.