MySQL Comparison Functions and Operator

Transcript

MySQL BETWEEN .... AND operator

MySQL BETWEEN AND operator checks whether a value is within a range. If the value is greater than or equal to the specific range separated by 'AND', it returns 1, otherwise it returns 0. Syntax : expr BETWEEN min AND max SELECT 7 BETWEEN 5 AND 10; ©w3resource Output : Example : The above example shows the value 7 is between 5 and 10 is true, so, it returns 1.

MySQL COALESCE() function

MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values. Syntax : COALESCE(value1,value2,value3,...) SELECT COALESCE(NULL, 2, 3); ©w3resource Output : Example : The above example shows the non-NULL value is 2, so, it returns 2.

MySQL COALESCE() function

SELECT COALESCE(NULL, NULL, NULL); ©w3resource Output : Example : The above example shows all the value in the argument is NULL. so, the return value is NULL.

MySQL NULL safe equal to operator

MySQL null safe equal to operator performs an equality comparison like the equal to (=) operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL. Syntax : <=> SELECT NULL <=> 1, NULL <=> NULL, 3 <=> NULL; ©w3resource Output : Example : The above MySQL statement compares if 1 is less than, equal to or greater than NULL; if NULL is less than, equal to or greater than NULL and if 3 is less than, equal to or greater than NULL.f

MySQL equal operator(=)

MySQL equal operator performs an equality comparison. Syntax : = SELECT 1 = 1, 1=2,NULL = NULL, NULL=3,3= NULL; ©w3resource Output : Example : The above MySQL statement checks if 1 is equal to 1, if 1 is equal to 2, if NULL is equal to NULL, if NULL is equal to 3 and if 3 is equal to NULL.

MySQL greater than or equal operator

MySQL greater than or equal operator checks whether one expression is either greater than or equal to another expression. If condition is true returns 1 otherwise return 0. Syntax : >= SELECT 5 >= 9, 5>=4; ©w3resource Output : Example : The above MySQL statement checks whether 5 is greater than equal to 9, and it fails and return 0 and in the other side whether 5 greater than equal to 4, and it comes true and returns 1.

MySQL greater than operator

MySQL greater than operator checks whether an expression is greater than another expression. It returns 1 if check comes to true otherwise returns 0. Syntax : > SELECT 5 > 9; ©w3resource Output : Example : The above MySQL statement checks whether 5 is greater than 9, and check comes to false and return 0.

MySQL GREATEST() function

MySQL GREATEST() function returns the greatest of the given arguments. Syntax : GREATEST(value1,value2,...) SELECT GREATEST(15,10,25); ©w3resource Output : Example : The above MySQL statement retrieves the greatest argument from the list of arguments.

MySQL IN() function

MySQL IN() function finds a match in the given arguments. The function returns 1 if the value to be matched is equal to any of the values in the IN list, otherwise returns 0. Syntax : expr IN (value1, value2, value3, ...) SELECT 10 IN(15,10,25); ©w3resource Output : Example : The above MySQL statement will returns 1 because the specified value is within the list of values.

MySQL INTERVAL() function

MySQL INTERVAL() function returns the index of the argument that is less than the first argument. It returns 0 if 1st number is less than 2nd number and 1 if 1st number is less than 3rd number and so on or -1 if 1st number is NULL. Syntax : INTERVAL(N,N1,N2,N3,...) SELECT INTERVAL(85, 1, 75, 17, 30,56, 175) ©w3resource Output : Example : The above MySQL statement explain that, the 1st number is 85, which is less than 175. All other numbers in the argument are smaller than 85. So, it returns the index of 175. Notice that indexing is started with the the 2nd number and first position is zero.

MySQL IS NOT NULL operator

MySQL IS NOT NULL operator will check whether a value is not NULL. Syntax : IS NOT NULL SELECT 5 IS NOT NULL,0 IS NOT NULL, NULL IS NOT NULL; ©w3resource Output : Example : The above MySQL statement explain that, the 1st number is 85, which is less than 175. All other numbers in the argument are smaller than 85. So, it returns the index of 175. Notice that indexing is started with the the 2nd number and first position is zero.

MySQL IS NOT operator

MySQL IS NOT operator will test a value against a boolean value. Boolean value can be TRUE, FALSE, or UNKNOWN. Syntax : IS NOT boolean_value SELECT 2 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; ©w3resource Output : Example : The above MySQL statement it is checked whether 2, 0 and NULL is not unknown.

MySQL IS NULL

MySQL IS NULL operator tests whether a value is NULL. If satisfied, then returns 1 otherwise returns 0. Syntax : IS NULL SELECT 2 IS NULL, 0 IS NULL, NULL IS NULL; ©w3resource Output : Example : In the above MySQL statement, it is checked whether 2, 0 and NULL are NULL, using IS NULL operator.

MySQL IS operator

MySQL IS operator tests a value against a Boolean value. Boolean value can be TRUE, FALSE, or UNKNOWN. Syntax : IS boolean_value SELECT 5 IS TRUE, 0 IS TRUE, NULL IS UNKNOWN; ©w3resource Output : Example : In the above MySQL statement, it is checked whether 5 is TRUE, 0 is TRUE and NULL is UNKNOWN using IS operator. For the first and third case it returns 1, for the second case it returns 0.

MySQL ISNULL() function

MySQL ISNULL() function returns 1 when the expression is NULL otherwise it returns 0. Syntax : ISNULL(expr) SELECT ISNULL(1+0); ©w3resource Output : Example : In the above MySQL statement, given argument is a non-NULL value. So , ISNULL function returns 0.

MySQL LEAST() function

MySQL LEAST() function returns the smallest argument from two or more arguments. Syntax : LEAST(value1,value2,...) SELECT LEAST(15,10,25); ©w3resource Output : Example : The above MySQL statement will find the smallest out of the list of arguments.

MySQL less than or equal operator

MySQL less than or equal operator checks whether an expression is either less than or equal to another expression. Syntax : < = SELECT 5 < 9; ©w3resource Output : Example : The above MySQL statement checks whether 5 is less than 9, and check comes to true and return 1.

MySQL less than operator

MySQL less than operator checks whether an expression is less than the other. Syntax : < SELECT 5 < 9; ©w3resource Output : Example : The above MySQL statement checks whether 5 is less than 9, and check comes to true and return 1.

MySQL LIKE operator

MySQL LIKE operator checks whether a specific character string matches a specified pattern. Syntax : expr LIKE pat [ESCAPE 'escape_char']

Sample table : country
country_name
India
Canada
Spain
Brazil
England

MySQL LIKE operator

Cont . . . ©w3resource

SELECT country_name FROM country WHERE country_name LIKE 'I%'; Output : Example : India The above MySQL statement scans the whole country table to find any country_name which starting with character ‘I’ followed by any characters. SELECT country_name FROM country WHERE country_name LIKE '%in'; Output : Example : Spain The above MySQL statement scans the whole country table to find any country which is ended with ‘in’ string.

MySQL LIKE operator

©w3resource SELECT country_name FROM country WHERE country_name LIKE '%an%'; Output : Example : The following MySQL statement scans the whole country table to find any country which have a string ‘an’ in his name. SELECT country_name FROM country WHERE country_name LIKE 'I_d_a'; Output : Example : India The following MySQL statement searches all countries whose name may be as ‘India’, ’Imdda’ etc. the underscore wildcard is used to mention single character.

MySQL NOT BETWEEN AND operator

MySQL NOT BETWEEN AND operator checks whether a value is not present between a starting and a closing expression. If present return 0 else return 1. Syntax : expr NOT BETWEEN min AND max SELECT 15 NOT BETWEEN 2 AND 10; ©w3resource Output : Example : The above MySQL statement checks that the value 15 is not present within 2 and 10, so returns 1.

MySQL not equal to (<>, !=) operator

MySQL Not equal is used to return a set of rows (from a table) after making sure that two expressions placed on either sides of the NOT EQUAL TO (<>) operator are not equal. Syntax : <> , != SELECT 5<>10; ©w3resource Output : Example : The above MySQL statement checks that whether the expression 5 is not equal to 10 is true or not , and results comes true, so return value is 1.

MySQL NOT IN() function

MySQL NOT IN() makes sure that the expression proceeded does not have any of the values present in the arguments. Syntax : expr NOT IN (value1, value2, value3, ...) SELECT 10 NOT IN(15,10,25); ©w3resource Output : Example : The above MySQL statement will returns 0 because the specified value is within the list of values, not condition is not satisfying here.

MySQL NOT LIKE operator

MySQL NOT LIKE is used to exclude those rows which are matching the criterion followed by LIKE operator. Syntax : expr LIKE pat [ESCAPE 'escape_char']

©w3resource

Sample table : country
country_name
India
Canada
Spain
Brazil
England

MySQL NOT LIKE operator

©w3resource

SELECT country_name FROM country WHERE country_name NOT LIKE 'I%'; Output : Example : The above MySQL statement scans the whole country table to find any country_name which starting with character ‘I’ followed by any characters.