Use '=' or LIKE to compare strings in SQL?

user_32043 0
Viewed 758610

There's the (almost religious) discussion, if you should use LIKE or '=' to compare strings in SQL statements.

  • Are there reasons to use LIKE?
  • Are there reasons to use '='?
  • Performance? Readability?
sql comparison
9 Answers

LIKE and the equality operator have different purposes, they don't do the same thing:
= is much faster, whereas LIKE can interpret wildcards. Use = wherever you can and LIKE wherever you must.

SELECT * FROM user WHERE login LIKE 'Test%';

Sample matches:

TestUser1
TestUser2
TestU
Test

soulmerge_445620

To see the performance difference, try this:

SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name = B.name

SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name LIKE B.name

Comparing strings with '=' is much faster.

Techmaddy_504340

In my small experience:

"=" for Exact Matches.

"LIKE" for Partial Matches.

Stu_Andrews_617800

There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):

ILIKE, which is a case insensitive LIKE match:

select * from people where name ilike 'JOHN'

Matches:

  • John
  • john
  • JOHN

And if you want to get really mad you can use regular expressions:

select * from people where name ~ 'John.*'

Matches:

  • John
  • Johnathon
  • Johnny
user_589910

For pattern matching use LIKE. For exact match =.

Techmaddy_504340

LIKE is used for pattern matching and = is used for equality test (as defined by the COLLATION in use).

= can use indexes while LIKE queries usually require testing every single record in the result set to filter it out (unless you are using full text search) so = has better performance.

user_337080

LIKE does matching like wildcards char [*, ?] at the shell
LIKE '%suffix' - give me everything that ends with suffix. You couldn't do that with =
Depends on the case actually.

Gishu_16950
Related