Equals(=) vs. LIKE

Question

When using SQL, are there any benefits of using = in a WHERE clause instead of LIKE?

Without any special operators, LIKE and = are the same, right?

Answer 1

Using = avoids wildcards and special characters conflicts in the string when you build the query at run time.

This makes the programmer's life easier by not having to escape all special wildcard characters that might slip in the LIKE clause and not producing the intended result. After all, = is the 99% use case scenario, it would be a pain to have to escape them every time.

rolls eyes at '90s

I also suspect it's a little bit slower, but I doubt it's significant if there are no wildcards in the pattern.

Answer 2

= and LIKE is not the same; = matches the exact string, while LIKE matches a string that may contain wildcards (%)

Answer 3

= doesn't grace your collation settings, LIKE does.

Answer 4

LIKE is not case sensitive and can use wild cards '%' and '_'.

Answer 5

Given, "united states of america" as a field in your DB

WHERE state = 'united'

would return nothing whereas

WHERE state LIKE 'united%'

would return results.

Answer 6

One difference - apart from the possibility to use wildcards with LIKE - is in trailing spaces: The = operator ignores trailing space, but LIKE does not.

Answer 7

Really it comes down to what you want the query to do. If you mean an exact match then use =. If you mean a fuzzier match, then use LIKE. Saying what you mean is usually a good policy with code.

Answer 8

Depends on the database system.

Generally with no special characters, yes, = and LIKE are the same.

Some database systems, however, may treat collation settings differently with the different operators.

For instance, in MySQL comparisons with = on strings is always case-insensitive by default, so LIKE without special characters is the same. On some other RDBMS's LIKE is case-insensitive while = is not.

Answer 9

like and = are different. Like is what you would use in a search query. it also allows wildcards like _ and %.

= should be used if you want exact matches. and it will be faster.

This site explains like

Answer 10

If you search for an exact match, you can use both, = and LIKE.

Using "=" is a tiny bit faster in this case (searching for an exact match) - you can check this yourself by having the same query twice in SQL Server Management Studio, once using "=", once using "LIKE", and then using the "Query" / "Include actual execution plan".

Execute the two queries and you should see your results twice, plus the two actual execution plans. In my case, they were split 50% vs. 50%, but the "=" execution plan has a smaller "estimated subtree cost" (displayed when you hover over the left-most "SELECT" box) - but again, it's really not a huge difference.

But when you start searching with wildcards in your LIKE expression, search performance will dimish. Search "LIKE Mill%" can still be quite fast - SQL Server can use an index on that column, if there is one. Searching "LIKE %expression%" is horribly slow, since the only way SQL Server can satisfy this search is by doing a full table scan. So be careful with your LIKE's !

Marc

Answer 11

The LIKE keyword undoubtedly comes with a "performance price-tag" attached. That said, if you have an input field that could potentially include wild card characters to be used in your query, I would recommend using LIKE only if the input contains one of the wild cards. Otherwise, use the standard equal to comparison.

Best regards...

Answer 12

The equals (=) operator is a "comparison operator compares two values for equality." In other words, in an SQL statement, it won't return true unless both sides of the equation are equal. For example:

SELECT * FROM Store WHERE Quantity = 200;

The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters." For example:

SELECT * FROM Employees WHERE Name LIKE 'Chris%';

LIKE is generally used only with strings and equals (I believe) is faster. The equals operator treats wild-card characters as literal characters. The difference in results returned are as follows:

SELECT * FROM Employees WHERE Name = 'Chris';

And

SELECT * FROM Employees WHERE Name LIKE 'Chris';

Would return the same result, though using LIKE would generally take longer as its a pattern match. However,

SELECT * FROM Employees WHERE Name = 'Chris%';

And

SELECT * FROM Employees WHERE Name LIKE 'Chris%';

Would return different results, where using "=" results in only results with "Chris%" being returned and the LIKE operator will return anything starting with "Chris".

Hope that helps. Some good info can be found here.

Answer 13

First answer is TERRIBLE.

LIKE and EQUALS are TWO ENTIRELY DIFFERENT OPERATORS.

EQUALS compares two pieces of data byte by byte. This means that equivalent strings in different encodings may not be equal, even though they look the same to the eye.

LIKE compares strings, and it takes into account the encoding of each string so as not to be fooled by different encodings of the same content.

There is no speed advantage, because the operators have different purposes!! This is like asking which is faster: an update or a delete? It's a nonsense question.

Use the operator which is correct for your use case, which for comparing strings is going to be the LIKE operator in 99% of cases.

References:

1. MySQL manual section 11.4.1.
2. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (section 8.5)

Answer 14

To address the original question regarding performance, it comes down to index utilization. When a simple table scan occurs, "LIKE" and "=" are identical. When indexes are involved, it depends on how the LIKE clause is formed. More specifically, what is the location of the wildcard(s)?

---

Consider the following:

CREATE TABLE test(
    txt_col  varchar(10) NOT NULL
)
go

insert test (txt_col)
select CONVERT(varchar(10), row_number() over (order by (select 1))) r
  from master..spt_values a, master..spt_values b
go

CREATE INDEX IX_test_data 
    ON test (txt_col);
go 

--Turn on Show Execution Plan
set statistics io on

--A LIKE Clause with a wildcard at the beginning
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '%10000'
--Results in
--Table 'test'. Scan count 3, logical reads 15404, physical reads 2, read-ahead reads 15416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index SCAN is 85% of Query Cost

--A LIKE Clause with a wildcard in the middle
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '1%99'
--Results in
--Table 'test'. Scan count 1, logical reads 3023, physical reads 3, read-ahead reads 3018, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost for test data, but it may result in a Table Scan depending on table size/structure

--A LIKE Clause with no wildcards
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO

--an "=" clause = does Index Seek same as above
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col = '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO


DROP TABLE test

There may be also negligible difference in the creation of the query plan when using "=" vs "LIKE".