MySQL Comparison Functions and Operator
This slides presentation describes the MySQL Comparison Functions and Operators briefly with syntax, examples and explanation.
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 . . . ©w3resourceSELECT 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']
©w3resourceSample table : country country_name India Canada Spain Brazil England
MySQL NOT LIKE operator
©w3resourceSELECT 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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/slides/mysql-comparison-functions-and-operator-slides-presentation.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics