MySQL BETWEEN, LIKE and IN Operators

MySQL BETWEEN, LIKE and IN are comparison operators that are used in where clause of DML statements. Let’s discuss them one by one.

MySQL BETWEEN Operator

The MySQL BETWEEN operator is used to check whether a value lies within a range. Syntax of using MySQL BETWEEN operator is

Expression BETWEEN min_val AND max_val

  • Expression is the expression or an operand whose value is to be checked in the given range.
  • BETWEEN and AND are the keywords
  • Min_val and max_val are the range values
  • The BETWEEN operator returns TRUE if the value of the expression is greater or equal than min_val and less than max_val. Otherwise BETWEEN operator returns FALSE.
  • The datatype of expression, min_val and max_val must be same. If their datatype are not same then automatic conversion of datatype is done before comparison.

Example

SELECT 1993 between 1000 AND 2000;
MySQL BETWEEN
SELECT 2993 between 1000 AND 2000;
MySQL BETWEEN FALSE

LIKE Operator

MySQL LIKE operator is used to check whether a value matches a pattern or not. Here no range is defined only pattern matching is done.  Syntax of using MySQL LIKE operator is

Expression LIKE pattern [ESCAPE ‘escape character’]

  • Expression is the expression or an operand whose value is to be checked against the given pattern.
  • LIKE and ESCAPE are the keywords. Use of ESCAPE is optional. It allows to ignore characters during comparison by LIKE operator.
  • Pattern is used to compare the operand or expression.
  • The LIKE operator returns TRUE if the value of the expression matches the given pattern. If operand or expression do not match pattern than LIKE operator returns FALSE. If expression or pattern is NULL then NULL is returned.
  • The pattern is given as a string. MySQL LIKE uses two wildcard characters to define the pattern to expand the range of comparison. The wild card characters are-
  • %  is used to represent any count of characters in the pattern.
  • _ is used to match exactly one character

Example

  • To find all artists whose names start with ‘A’ you can compare artist_name with ‘D%’ using LIKE operator. 
SELECT * FROM artists WHERE artist_name LIKE 'D%';
MySQL Like
  • To find all artists whose names have ‘a’ in it use ‘%a%’
SELECT * FROM artists WHERE artist_name LIKE '%a%';
MySQL Like
  • To find all artists whose names have ‘a’ as second character use ‘_a%’
SELECT * FROM artists WHERE artist_name LIKE '_a%';
MySQL Like
  • To find all artists whose name start with ‘a’ and end with ‘k’ use ‘D%y’
SELECT * FROM artists WHERE artist_name LIKE 'D%y';
MySQL Like
  • To find all artists whose name has only five characters use ‘_ _ _ _ _’
SELECT * FROM artists WHERE artist_name LIKE '_____';
MySQL Like

IN Operator

The MySQL IN operator is used to check is a value matches one or more values in a given list of values. Syntax of using MySQL IN operator is

Expression IN (list of values)

  • Expression is the expression or an operand whose value is to be found within the list of given values.
  • IN is the keyword
  • List of values must contain elements matching the datatype of the expression or operand.
  • The IN operator returns TRUE if the value of the expression matches any one value in the given list. Else IN operator returns FALSE.

Example

SELECT * FROM artists WHERE artist_id IN (3,5,6);
MySQL IN