What is the difference between the LIKE operator and the equal sign (=) in MySQL/MariaDB. How to use LIKE in SQL

Table of contents

1. What to use to search in MySQL/MariaDB: LIKE operator or equals (=) operator

2. When to use equal sign (=) in SQL

3. When to use the LIKE operator in SQL

4. Even more differences under the hood

Conclusion

What to use to search in MySQL/MariaDB: LIKE operator or equals (=) operator

The LIKE operator and equals (=) are used in SQL queries to search for strings and numbers. Moreover, sometimes they can be interchangeable, as in the following examples, which give the same result.

Example using LIKE:

SELECT * FROM `TestTABLE` WHERE `id` LIKE '1';

Example using the equals sign:

SELECT * FROM `TestTABLE` WHERE `id` = '1';

This post will explain the difference between LIKE and equals (=) and show examples of their use in MySQL and MariaDB.

When to use equal sign (=) in SQL

The equal sign (=) in SQL queries is used to find an exact match of a string or number. Expressions with equals cannot use wildcards.

Moreover, when speaking of “exact match,” you should always remember about Collation, which is also used when performing a search using the equal sign (=). This means that case and accent marks are not taken into account. For example, SQL query

SELECT * FROM `TestTABLE` WHERE `test_column` = 'THIS IS A TEST STRING';

Finds the following line (the content is the same, but the letter case is different):

This is a test string

The search string, when using the equal sign, cannot contain wildcard characters.

When to use the LIKE operator in SQL

The LIKE operator can search for either an exact match or a wildcard search. When using the LIKE operator, Collation is also used, meaning case and accent marks are not taken into account.

The LIKE operator accepts the following wildcard characters:

• The percent sign (%) represents zero, one, or more characters. That is, it can be in the range “from nothing to anything”. It can be any number of characters, for example, one hundred letters “A”. Or it could be a complete absence of characters.
• The underscore (_) represents exactly one character.

The following example will find the string “This is a test string”:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE 'This is a test%';

And the following option with the “_” character will not find the test string, since there should only be one character after the word “test”:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE 'This is a test_';

But if we use six underscores, the string “This is a test string” will be found again:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE 'This is a test_______';

Wildcards can appear in any part of the search pattern (at the end, at the beginning, inside):

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE '%test%';

Wildcards can be combined:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE '%t__t%';

You can use any number of wildcards and alternate them with other symbols and letters:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE '%an%rs%';

Even more differences under the hood

In addition to the differences already described, there are differences in the comparison (search) process – they do not affect the final result, and do not even affect performance if you are working with small or medium-sized databases.

However, if you are working with very large databases, then you will need to drill down to the MySQL/MariaDB source code level. Or simply run tests to evaluate the performance of alternative SQL queries.

Fortunately, most users do not need to know such details, since, we repeat, the search result does not depend on them.

Conclusion

So, the LIKE operator and the equals (=) operator have the following things in common:

1. Can be used to search for numbers or strings
2. They use Collation, that is, they search without taking into account the case of letters

Differences between the LIKE operator and the equal sign (=):

1. The LIKE operator can use the wildcards “%” and “_”, but the equal sign cannot use them
2. The LIKE operator can find a string based on the substring it searches for, while the equals (=) operator can only search for exactly matching strings

If you are working with very large tables and you can use alternative queries with LIKE or equal sign, then you need to run tests to evaluate the performance and choose the most appropriate search method. Regular users don't need to worry about this.