Some others used comparison operators are – BETWEEN, LIKE, IS.

One of the projects in our website (microcodes.in), Chatbot System, is also built upon the LIKE operator.

BETWEENCompares two operands and returns something
LIKEIt checks similar strings present in filed or not
ISUses for NULL or NOT NULL checks

BETWEEN operator

The BETWEEN operator in MySQL is used to retrieve values within a specified range. It is often used in a WHERE clause to filter rows based on a range of values for a particular column. The BETWEEN operator is inclusive, meaning that it includes both the start and end values in the range. For example, to retrieve all customers with an age between 18 and 30, you can use the following query: “SELECT * FROM customers WHERE age BETWEEN 18 AND 30;”. This will return all customers whose age is between 18 and 30, including both 18 and 30.

We can use BETWEEN operator using SELECT statement.

Syntax:

SELECT column1,column2,…. FROM table WHERE column_name BETWEEN value1 AND value2;

Let’s consider another tables’ example:

let’s sort those students whose age between 20-25 using BETWEEN operator.

LIKE operator

The LIKE operator in MySQL is used for pattern matching within string values. It is commonly used in the WHERE clause of a SQL query to filter rows based on a specified pattern.

The LIKE operator allows you to use wildcard characters to match patterns:

  1. The percent sign (%) represents zero, one, or multiple characters.
  2. The underscore (_) represents a single character.

By using wildcard characters such as % and _, you can define flexible search patterns. Here are some common use cases for the LIKE operator:

  1. Searching for a specific prefix: You can use the % wildcard to match any number of characters at the beginning of a string. For example, column_name LIKE ‘prefix%’ will match all rows where column_name starts with the specified prefix.
  2. Searching for a specific suffix: You can use the % wildcard at the end of the pattern to match any number of characters at the end of a string. For example, column_name LIKE ‘%suffix’ will match all rows where column_name ends with the specified suffix.
  3. Searching for a specific substring: By placing the % wildcard on both sides of the pattern, you can match a specific substring anywhere within the string. For example, column_name LIKE ‘%substring%’ will match all rows where column_name contains the specified substring.

In simple words, I can say,

  • a%  =>  find those records in column name’s(attribute) which start with character ‘a’
  • %w  =>  find those records which ends with ‘w’
  • ‘_a%’  =>  escaping first character, from 2nd character start from ‘a’
  • ‘%a_’  =>  shows those records which 2nd last character ends with character ‘a’

Syntax:

SELECT column1,column2,… FROM table WHERE column_name LIKE ‘%string name%’;

Let’s consider the below table:

Find those employees which name’s end with ‘suneja’.

Find those employees which name’s start with character ‘a’

MySQL wildcards: MySQL wildcards are characters that help search data matching complex criteria. Wildcards basically using LIKE operator with HWERE clause.

Note: LIKE does very simple wildcard matches, but ‘REGEXP’ does complex wildcard matches.

Usage of REGEXP:

Syntax:

SELECT column1,column2,… FROM table WHERE column_name REGEXP ‘pattern’;

The REGEXP operator in MySQL is used to perform regular expression pattern matching on strings. It can be used in the WHERE clause of a SELECT statement to filter rows that match a specific pattern.

  • ‘^a’  =>  it find those records which starting letter is ‘a’
  • ‘a$’  =>  it finds those records which ending letter is ‘a’

Let’s consider below table:

Let’s see the usage of REGEXP usages:

But wait, if we can done some work using LIKE operator, then why you need to use REGEXP instead of LIKE operator.

Say, you want to find multiple wildcards matches at a time, then you need to use REGEXP instead of LIKE operator.

Syntax:

SELECT  column1,column2,…  FROM  table  WHERE  column_name  REGEXP  ‘^a|^s’;

Let’s find those records where string pattern start with ‘a’ or ‘s’ both. I any pattern matches then shows whole record.

Let’s find those country names which start with either a or b.

IS operator

This operator especially for checking if a field contains NULL or NOT NULL. MySQL does not have a built-in IS operator like some other programming languages do. In MySQL, you typically use comparison operators (such as =, <>, <, >, <=, >=) to compare values in queries. The comparison operators allow you to check for equality, inequality, or various other conditions between columns and values.

To check if a column equals a specific value

SELECT * FROM table_name WHERE column_name = ‘some_value’;

Syntax –

SELECT attribute_names FROM table_name WHERE attribute_name IS ?;

‘NULL’ keyword basically used to check whether any column filled by null values or not.

Syntax:

SELECT column1,column2,… FROM table WHERE column IS NULL;

     It will lists down all the columns which contains null values.

Syntax:

SELECT column1,column2,… FROM table WHERE column IS NOT NULL;

     It will lists down all the columns which does not contains null values.

Let’s consider the below table

 Let’s see usages of NULL or NOT NULL vales.

download notes