What is the significant difference between MySQL LIKE and equal to (=) operator?

MySQLMySQLi Database

We have seen the MySQL SELECT command to fetch data from the MySQL table. We can also use a conditional clause called as the WHERE clause to select the required records.

A WHERE clause with the ‘equal to’ sign (=) works fine where we want to do an exact match. Like if "tutorial_author = 'Sanjay'". But there may be a requirement where we want to filter out all the results where the tutorial_author name should contain "jay". This can be handled using MySQL LIKE operator along with the WHERE clause.

If the MySQL LIKE operator is used without a wildcard character, the LIKE operator is very same as the equal to sign along with the WHERE clause.

Example

mysql> Select * from Student Where Name LIKE 'Harshit';
+------+---------+---------+----------+--------------------+
| Id   | Name    | Address | Subject  | year_of_admission  |
+------+---------+---------+----------+--------------------+
| 15   | Harshit | Delhi   | Commerce |       2009         |
+------+---------+---------+----------+--------------------+
1 row in set (0.00 sec)

mysql> Select * from Student Where Name = 'Harshit';
+------+---------+---------+----------+--------------------+
| Id   | Name    | Address | Subject  | year_of_admission  |
+------+---------+---------+----------+--------------------+
| 15   | Harshit | Delhi   | Commerce |      2009          |
+------+---------+---------+----------+--------------------+
1 row in set (0.00 sec)

Other than the difference of having wildcard characters, %, and _, there is a significant difference between LIKE and = operators is that LIKE operator does not ignore the trailing spaces while = operator ignores the trailing spaces.

Example

mysql> Select * from Student Where Name = 'Harshit ';
+------+---------+---------+----------+--------------------+
| Id   | Name    | Address | Subject  | year_of_admission  |
+------+---------+---------+----------+--------------------+
| 15   | Harshit | Delhi   | Commerce |       2009         |
+------+---------+---------+----------+--------------------+
1 row in set (0.00 sec)

mysql> Select * from Student Where Name LIKE 'Harshit ';
Empty set (0.00 sec)
raja
Published on 20-Feb-2018 14:40:44
Advertisements