Postgres LIKE vs EQUAL

Introduction

The LIKE and EQUAL conditions are used to compare data from the database using matching techniques. LIKE condition uses pattern matching while the EQUAL condition compares the input with the original data to ensure they are equal. Thus, to obtain a true case using EQUAL, both the input data and the original data must be same, otherwise it is not true. On the other hand, the LIKE condition is true if part of the original data matches the input data. It is false otherwise if no part of the original data matches the input data.

To bring out the difference between the two we will consider a scenario where a school database administrator requires searching for an alumni student details from the school database. In some cases, the students will remember their complete registration number and in other cases, the students will remember part of their registration number. To enable the administrator to get the students who remembers only part of their registration number, one can use the LIKE condition to filter the list of the students who have similar records. Thus in most case, LIKE is used to obtain matching patterns while EQUAL is used to obtain equivalent patterns. LIKE is mostly used in Google search engine, or searching matching words in most editing tools like Microsoft office.

Stick with us as we examine PostgreSQL LIKE vs EQUAL and look at some practical examples together with the WHERE clause.

LIKE condition

The LIKE condition is often used when filtering rows with string data. The syntax to filter data is as follows:

string LIKE pattern;

A query returns true if the string matches the given pattern. Otherwise, it returns false. Additionally, you can still combine the like with negation operator NOT to obtain NOT LIKE patterns. NOT LIKE list the strings which do not match the given pattern.

Application example

We shall use the student table to illustrate the application of LIKE in PostgreSQL. We first select the data to see what is in the table


GeSHi Error: GeSHi could not find the language js (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Now we select the list of student whose name matches the pattern “ti” using the following query;

SELECT* FROM student WHERE name LIKE '%ti%';
Results
 id |  name   | age | gender | marks
----+---------+-----+--------+-------
  1 |  tito   |  15 | MALE   |    68
  5 |  tito   |  15 | MALE   |    68
  6 |  ticoon |  15 | MALE   |    68
(3 ROWS)

Image from Gyazo

In other cases, you can use an underscore and the percent symbol to search for specific patterns preceded by any other character. Example

SELECT* FROM student WHERE name LIKE '%_to%';

Results

Image from Gyazo

EQUAL condition

The equal condition filters the string which is completely identical to the given patern. We use the following statement to obtain equal patterns;

String = pattern

Example

SELECT * FROM student WHERE name = ' QUEEN';

Results Image from Gyazo

The result does not indicate any result when there is no string matching the given pattern as shown below.

SELECT * FROM student WHERE name = ' QUEN';

Image from Gyazo

Therefore, EQUAL is used when we need to obtain specific rows with details equal to input string.

Conclusion

Thank you for joining us. We hope you now have a greater understanding of LIKE vs EQUAL in SQL.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.