SQL LIKE with no wildcards the same as '='?

Question

I know this is a pretty basic question, and I think I know the answer...but I'd like to confirm.

Are these queries truly equivalent?

SELECT * FROM FOO WHERE BAR LIKE 'X'
SELECT * FROM FOO WHERE BAR ='X'

Perhaps there is a performance overhead in using like with no wild cards?

I have an app that optionally uses LIKE & wild cards. The SP currently does the like and appends the wild cards -- I am thinking of just updating the query to use like but have the app append the wild cards as needed.

Married

Answer 1

As @ocdecio says, if the optimizer is smart enough there should be no difference, but if you want to make sure about what is happening behind the scenes you should compare the two query's execution plans.

Nolanolan

Very true, the execution plan is your friend. – Ilona

Be careful, too, as this may relate to indexes. – Despond

This is wrong answer. Not only there is a performance overhead with LIKE, but also there is a logical difference. See answer of CSharpie below. – Caliph

Answer 2

Original Answer by Matt Whitfield from here

There is a difference between = and LIKE. When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces.

So if you have a column that is char or nchar and not nvarchar or varchar, there will be different results due to trailing spaces.

Small example to reproduce this behaviour:

CREATE TABLE #temp (nam [varchar](MAX))
INSERT INTO [#temp] ([nam])
VALUES ('hello')
INSERT INTO [#temp] ([nam])
VALUES ('hello  ')

SELECT * FROM #temp WHERE [nam] = 'hello  '

SELECT * FROM #temp WHERE [nam] LIKE 'hello  '

Retrench

Important note. When this query is run with a parameter instead of an embedded value (the way it should be done) using LIKE vs =, the EQUAL search performs substantially better. This is because the query with LIKE does not know if the parameter contains wildcards or not and so must perform a Full Index Scan vs an index seek or key lookup. I understand that the query in this question does not use parameters, but I wanted to note this because using parameters is important to help avoid SQL injection attacks. – Apologia

And there is a performance penalty: LIKE takes ~1.45 times more time than '='. – Caliph

Answer 3

Any good optimizer would reduce the first expression to the second.

Farinose

Well, maybe, but if it's a parameterized query where the query plan is reused, and the parameters sometimes have wildcards and sometimes not, that wouldn't be necessarily a good idea. Either way, there should be no performance penalty. – Nesmith

@le dorfier: I agree, in the generic case that is true. In the very specific and narrow case above, well, that's an easy one for the optimizer. – Ilona

I'm not so sure the optimise can handle this for parm'd queries since like won't use any index – Baptiste

LIKE uses indexes as long as there's no wildcard at the beginning of the search string. – Nesmith

Answer 4

I found one case where using LIKE is 20x - 125x faster than using = (which goes to show that armchair philosophizing about "what the optimizer would do" is not as easy as some would think, or people vastly overestimate the intelligence of these tools even on very simple tasks).

It's a database with ~50 million rows.

Create a trigram GIN index:

CREATE EXTENSION pg_trgm;
CREATE INDEX  ON profiles  USING gin (username gin_trgm_ops);

And now one query takes 200 ms, while the the other takes 4 - 25 secs.

SELECT userid,username,country  FROM profiles  WHERE username LIKE 'someuser';
SELECT userid,username,country  FROM profiles  WHERE username='someuser';

Phosphide