When to Use Equals (=) vs. LIKE in SQL Queries

1. Overview

SQL databases provide powerful tools for data retrieval and manipulation, with operators playing a crucial role in how we interact with stored information. Among these operators, Equals (=) and LIKE are fundamental for querying data. However, their behavior isn’t uniform across all database systems.

In this tutorial, we’ll explore the differences between the Equals (=) and LIKE operators, including how they differ between MySQL, MSSQL, and PostgreSQL.

2. The Equals (=) Operator

At the core of SQL queries is the ability to compare data values, and the Equals (=) operator is a primary tool for this purpose. This operator allows us to retrieve records where a specified column exactly matches a specified value.

2.1. Our Example Table

Let’s take a look at the layout of our Student test table, featuring sample data from the University database:

SELECT * FROM Student;
+------+-----------------+-------------+------------+-----------------+-----------------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date |
+------+-----------------+-------------+------------+-----------------+-----------------+
| 1001 | John Liu        |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |
| 1003 | Rita Ora        |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |
| 1007 | Philip Lose     |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |
| 1010 | Samantha Prabhu |  3217165566 | 2001-03-21 | 2020-01-15      | 2024-06-15      |
| 1011 | Vikas Jain      |   321345662 | 2001-07-18 | 2020-01-15      | NULL            |
[...]

Our goal is to select a row in the table using the Equals (=) operator.

2.2. String Comparison

Using the Equals (=) operator is quite intuitive. A query like this works on all databases:

SELECT *
FROM Student
WHERE name = 'John Liu';

It selects the first row of the table:

+------+----------+-------------+------------+-----------------+-----------------+
| id   | name     | national_id | birth_date | enrollment_date | graduation_date |
+------+----------+-------------+------------+-----------------+-----------------+
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |
+------+----------+-------------+------------+-----------------+-----------------+

However, there are subtle differences between DBMS that can make our queries unportable from one database to another:

The main difference that can cause confusion is case and accent sensitivity or insensitivity, which can lead to unpredictable results. Basically, a collation is a set of rules that determines how string data is sorted and compared in a database. It defines the character set used and specifies how characters are compared, including aspects such as case sensitivity and accent sensitivity.

Here is the collation of the name column in our Student table:

• MySQL → utf8mb3_general_ci (case-insensitive and accent-insensitive)
• MSSQL → SQL_Latin1_General_CP1_CI_AS (case-insensitive but accent-sensitive)
• PostgreSQL → it_IT.UTF-8 (case-sensitive and accent-sensitive)

For example, let’s say we want to search for ‘jòhn liù’ instead of ‘John Liu’:

SELECT *
FROM Student
WHERE name = 'jòhn liù';

Such a query returns a one row result in MySQL, but a zero row result in MSSQL and PostgreSQL.

Finally, regarding identifier delimiters, such as Student vs. “Student”, the only way to write portable code is to not use them. In fact, as we saw in the previous table, not only do delimiters differ from database to database, but in the case of PostgreSQL they change case sensitivity.

2.3. Using Equals (=) With Numeric Data

Numeric comparisons using the Equals (=) operator are generally straightforward and consistent across MySQL, MSSQL, and PostgreSQL:

SELECT *
FROM Student
WHERE id = 1001;

However, differences in numeric data types such as INT and BIGINT can cause problems. Each database defines these types with specific ranges and precision. Comparing very large numbers or different numeric types can lead to overflow errors, implicit type casting, or unexpected results.

2.4. Using Equals (=) With Date and Time Data

Comparing dates or searching within a date range is also quite easy:

SELECT *
FROM Student
WHERE birth_date = '2001-04-05';

SELECT *
FROM Student
WHERE birth_date >= '2001-04-05'
      AND birth_date <  '2001-04-06';

These two queries return the desired result, the first row of the table, in all three databases. A different discussion, however, if we also include the time:

SELECT *
FROM Student
WHERE birth_date = '2001-04-05 08:30:00';

MySQL returns no results because it considers the time of all dates in birth_date to be midnight. Instead, in this case, MSSQL and PostgreSQL behave as if the time isn’t there.

The issue becomes even more complex when we want to consider time zones. Each database would require a separate in-depth discussion, but we won’t go into that.

3. The LIKE Operator

The LIKE operator is a powerful SQL tool for pattern matching within string data. Its case sensitivity typically mirrors that of the Equals (=) operator described earlier.

LIKE is generally not applicable to numbers or dates unless they’re converted to strings. However, such conversion isn’t common practice and can cause performance problems.

3.1. Wildcard Characters

LIKE uses two wildcard characters to define patterns:

• % (percent sign) → Represents zero or more characters
• _ (underscore) → Represents a single character

For example, let’s search for all students whose names begin with S:

SELECT *
FROM Student
WHERE name LIKE 'S%';

The result is the same in all three databases:

+------+-----------------+-------------+------------+-----------------+-----------------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date |
+------+-----------------+-------------+------------+-----------------+-----------------+
| 1010 | Samantha Prabhu |  3217165566 | 2001-03-21 | 2020-01-15      | 2024-06-15      |
| 1619 | Sam Roberts     |  9203455662 | 2002-06-21 | 2021-01-15      | 2025-06-15      |
| 1719 | Siren Lobo      |   189091342 | 2002-06-17 | 2021-01-15      | NULL            |
+------+-----------------+-------------+------------+-----------------+-----------------+

Or let’s try searching for students with first and last names beginning with R, where the last name is exactly three characters:

SELECT *
FROM Student
WHERE name LIKE 'R% R__';

Again, we get a consistent result across databases:

+------+-----------+-------------+------------+-----------------+-----------------+
| id   | name      | national_id | birth_date | enrollment_date | graduation_date |
+------+-----------+-------------+------------+-----------------+-----------------+
| 1103 | Rose Rit  |  1323612067 | 2001-05-14 | 2020-01-15      | NULL            |
| 1610 | Ritu Raj  |  3203455662 | 2002-02-05 | 2021-01-15      | 2025-06-15      |
| 2001 | Reena Roy |  9023455613 | 2003-01-13 | 2022-01-15      | 2025-06-15      |
+------+-----------+-------------+------------+-----------------+-----------------+

Unlike MSSQL, both PostgreSQL and MySQL extend the pattern matching capabilities by supporting regular expressions through specific operators. PostgreSQL uses operators such as ~ (matches regex) and ~* (matches regex case-insensitively), while MySQL uses the REGEXP or RLIKE operators for regular expression matching.

3.2. LIKE vs. Equals (=)

According to the SQL standard, the LIKE operator matches on a character-by-character basis, so trailing spaces are always significant. This can lead to different results than with the Equals (=) operator. Let’s try it with MySQL:

SELECT *
FROM Student
WHERE name = 'Agatha Christi    ';
+------+----------------+-------------+------------+-----------------+-----------------+
| id   | name           | national_id | birth_date | enrollment_date | graduation_date |
+------+----------------+-------------+------------+-----------------+-----------------+
| 2006 | Agatha Christi |  1100245767 | 2003-05-19 | 2022-01-15      | NULL            |
+------+----------------+-------------+------------+-----------------+-----------------+
1 row in set (0,00 sec)

SELECT *
FROM Student
WHERE name LIKE 'Agatha Christi    ';
Empty set (0,00 sec)

MSSQL returns the same results, but PostgreSQL doesn’t. This is because the Equals (=) operator in MySQL and MSSQL may ignore trailing spaces depending on the data type and the pad attribute of the collation. In contrast, PostgreSQL treats trailing spaces as significant for both LIKE and Equals comparisons.

Aside from this difference, although Equals (=) and LIKE can produce the same results, Equals (=) is generally more optimized and efficient than LIKE because it performs an exact match without the need for pattern matching, which makes it faster, especially for large datasets.

4. Conclusion

In this article, we explored how the Equals (=) and LIKE operators work in SQL across different database systems, including MySQL, MSSQL, and PostgreSQL. We looked at their behavior with string, numeric, and date data types, highlighting how factors like case sensitivity, collations, and wildcard usage can affect query results.

While LIKE without wildcards and Equals (=) can produce the same results, it’s wise to use the operator that best reflects the intent of the query. If we want an exact match, the Equals (=) operator is more appropriate and can provide better performance and code clarity.