Loading

MySQL Comparison Functions

BETWEEN min AND max:- BETWEEN Operator consists of two values first for begin and lasts for the end. That means the BETWEEN operator selects values from the given rang. For example:-

SELECT * FROM student WHERE age BETWEEN 12 AND 20;

It will list all values which will include 12 and 20.

SELECT * FROM product WHERE price BETWEEN 30 AND 60;

SELECT * FROM employee WHERE salary BETWEEN 2000 AND 5000;

NOT BETWEEN min AND max:- It is opposite to BETWEEN Operator. It will list all values which will not be in the given range.

SELECT * FROM student WHERE age NOT BETWEEN 12 AND 20;

It will not list all those values which will include 12 and 20.

SELECT * FROM product WHERE price NOT  BETWEEN 30 AND 60;

SELECT * FROM employee WHERE salary NOT  BETWEEN 2000 AND 5000;

COALESCE(value,…):- COALESCE() function is used to get the first NOT NULL value from the given query. If all the values of the given list will be NULL then it will return NULL.

SELECT COALESCE(NULL, NULL, ‘kshtutor.com’, NULL, ‘demotest.com’);

OUTPUT:- kshtutor.com

SELECT COALESCE(‘kshtutor.com’, NULL, ‘demotest.com’,  NULL, NULL);

OUTPUT:- kshtutor.com

SELECT COALESCE(NULL, 3, 5, ‘kshtutor.com’);

OUTPUT:-3

GREATEST(value1,value2,…):- GREATEST function return the greatest value from the given list or argument. For Example:-

SELECT GREATEST(10, 9, 12, 8, 1, 13, 7, 15, 2);

OUTPUT:- 15

SELECT GREATEST(‘kshtutor.com’, ‘exampletest.com’, ‘testour.com’);

OUTPUT:-  exampletest.com

IN (value,…):- IN operator is a conditional operator which returns values from the given list of values. It is similar to using multiple OR. That means It replaces multiple OR. For example:-

SELECT name, age FROM student WHERE section IN(‘A’, ‘B’, ‘C’);

OUTPUT:- It will return all ‘names’ and ‘ages’ of the students whose section will be related to ‘A’  or ‘B’ or ‘C’.

NOT IN (value,…):- NOT IN operator is just the opposite of IN operator.

SELECT name, age FROM student WHERE section NOT IN(‘A’, ‘B’, ‘C’);

OUTPUT:- It will not return all ‘names’ and ‘ages’ of the students whose section will be related to ‘A’  or ‘B’ or ‘C’.

INTERVAL(N, N1, N2, N3,…):- The INTERVAL function is mainly used for date and time values. It is based on a binary search algorithm that returns values from 0 to N. For example:-

SELECT ‘2020-02-7’ + INTERVAL -5 DAY AS DATE

OUTPUT:- 2020-02-02

DATE_SUB and DATE_ADD functions to add or subtract date/month/time from a date value.

IS:-  Mysql IS operator test a value and assigns a Boolean value. A Boolean value can be true, false, or unknown. We can check numeric and NULL values using this operator. For example:-

SELECT 3 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;

-> 1, 1, 1

SELECT * FROM employee WHERE salary IS true.

list all the employee’s detail whose salary will be in positive integers.

IS NOT:- It is just the opposite of IS operator. For example

SELECT * FROM employee WHERE salary IS true.

list all the employee’s detail whose salary will not be in a positive integer.

SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;

-> 1, 1, 0

IS NULL:-  IS NULL operator checks the value is NULL That means it checks the empty value. For example:-

SELECT * FROM student WHERE annualfee IS NULL

It will return all details of students whose Annual fee is not deposited.

IS NOT NULL:- IS NOT NULL is the reverse of IS NULL.

SELECT * FROM student WHERE annualfee IS NOT NULL

It will return all details of students whose Annual fee is deposited.

ISNULL(expreson ):- It is similar to IS NULL. If the expression is NULL, ISNULL() returns 1, otherwise, it returns 0.

mysql> SELECT ISNULL(1+1);

-> 0

mysql> SELECT ISNULL(1/0);

-> 1

LEAST(value1,value2,…):- It will return the lowest value from the list of arguments. For Example:-

SELECT LEAST(5,8,9,12, 4, 18,3);

-> 3

SELECT LEAST(“kshtutor.com”, “redtest.com”, “testblack.com”);

-> kshtutor.com

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w