MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Funtions and Operators / Question No: 230

What are comparison functions in MySQL

Answer No: 230

Comparison functions enable you to test relative values or membership of one value within a set of values. LEAST() and GREATEST() take a set of values as arguments and return the one that is smallest or largest, respectively:

mysql> SELECT LEAST(4,3,8,-1,5), LEAST('cdef','ab','ghi');
+-------------------+--------------------------+
| LEAST(4,3,8,-1,5) | LEAST('cdef','ab','ghi') |
+-------------------+--------------------------+
|                -1 | ab                       |
+-------------------+--------------------------+

mysql> SELECT GREATEST(4,3,8,-1,5), GREATEST('cdef','ab','ghi');
+----------------------+-----------------------------+
| GREATEST(4,3,8,-1,5) | GREATEST('cdef','ab','ghi') |
+----------------------+-----------------------------+
|                    8 | ghi                         |
+----------------------+-----------------------------+

INTERVAL Function

INTERVAL() takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.

mysql> SELECT INTERVAL(2,1,2,3,4);
+---------------------+
| INTERVAL(2,1,2,3,4) |
+---------------------+
|                   2 |
+---------------------+

mysql> SELECT INTERVAL(0,1,2,3,4);
+---------------------+
| INTERVAL(0,1,2,3,4) |
+---------------------+
|                   0 |
+---------------------+

mysql> SELECT INTERVAL(6.3,2,4,6,8,10);
+--------------------------+
| INTERVAL(6.3,2,4,6,8,10) |
+--------------------------+
|                        3 |
+--------------------------+

IN Function

It's sometimes necessary to determine whether a value is equal to any of several specific values. One way to accomplish this is to combine several equality tests into a single expression with the OR logical operator:

... WHERE id = 13 OR id = 45 OR id = 97 OR id = 142
... WHERE name = 'Tom' OR name = 'Dick' OR name = 'Harry'

However, MySQL provides an IN() operator that performs the same kind of comparison and that is more concise and easier to read. To use it, provide the comparison values as a comma-separated list of arguments to IN():

... WHERE id IN(13,45,97,142)
... WHERE name IN('Tom','Dick','Harry')

Using IN() is equivalent to writing a list of comparisons with OR, but IN() is much more efficient. Arguments to IN() may be of any type (numeric, string, or temporal), although generally all values given within a list should all have the same type. Elements in a list may be given as expressions that are evaluated to produce a value. If the expression references a column name, the column is evaluated for each row.

IN() always returns NULL when used to test NULL. That is, NULL IN(list) is NULL for any list of values, even if NULL is included in the list. This occurs because NULL IN(NULL) is equivalent to NULL = NULL, which evaluates to NULL.

BETWEEN Operator

IN() tests membership within a set of individual values. If you're searching for a range of values, a range test might be more suitable. The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them:

... WHERE id BETWEEN 5 AND 10

The comparison is inclusive, so the preceding expression is equivalent to this one:

... WHERE id >= 5 AND id <= 10

Related MySQL FAQs to the Above FAQ

What-is-MySQL-date-format-and-its-specifiers What is MySQL date format and its specifiers?

How-to-get-comma-separated-all-values-in-a-single-row-against-each-ID How to get comma separated all values in a single row against each ID?

How-to-concat-values-in-MySQL How to concat values in MySQL?

How-to-convert-time-zone-in-MySQL How to convert time zone in MySQL?

How-to-set-conditions-on-query-data-with-CASE-construct How to set conditions on query data with CASE construct?

How-to-set-control-conditions-on-query-data-with-IF-funtion How to set control conditions on query data with IF funtion?

What-are-mathematical-functions-in-MySQL What are mathematical functions in MySQL?

How-are-aggregate-functions-in-MySQL How are aggregate functions in MySQL?

What-are-string-functions-in-MySQL What are string functions in MySQL?

What-are-temporal-or-date-time-functions-in-MySQL What are temporal or date time functions in MySQL?

What-are-NULL-value-related-functions-in-MySQL What are NULL value related functions in MySQL?

How-does-union-work-in-MySQL How does union work in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.