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