What's the difference between "LIKE" and "=" in SQL?

Question

Is there any difference between:

SELECT * FROM users WHERE username="davyjones"

and

SELECT * FROM users WHERE username LIKE "davyjones"

possible duplicate of [Equals(=) vs. LIKE](http://stackoverflow.com/questions/543580/equals-vs-like) — Mark E. Haase, Aug 01 '14 at 19:32
Here, the coming output is same but if i talk about the time consumption then where clause is take more time then the like clause if i imagine that this query is executing on a large data set. — Mukkarram Moin, Dec 05 '20 at 08:38

score 48 · Answer 1 · edited Mar 08 '18 at 09:44

48

LIKE allows partial matching / use of wildcards, while = checks for exact matches.

For example

SELECT * FROM test WHERE field LIKE '%oom';

Will return rows where field value is any of the following:

Zoom, Boom, Loom, Groom

edited Mar 08 '18 at 09:44

answered Oct 01 '09 at 16:26

code_burgar

12,025
4
35
53

1

It won't return those rows if column is CHAR(5) instead of VARCHAR(5). – Milan Babuškov Oct 01 '09 at 16:39
1

@codeburger: MySQL has years before it gets anywhere near being SQL standard compliant ;) – Milan Babuškov Oct 02 '09 at 10:14
@MilanBabuškov Its still nice to say it, MySQL is a free good DBMS for small projects! – Kil jeaden Dec 14 '19 at 18:14

Milan Babuškov · Accepted Answer · 2009-10-01T16:41:16.307

41

As per SQL standard, the difference is treatment of trailing whitespace in CHAR columns. Example:

create table t1 ( c10 char(10) );
insert into t1 values ('davyjones');

select * from t1 where c10 = 'davyjones';
-- yields 1 row

select * from t1 where c10 like 'davyjones';
-- yields 0 rows

Of course, assuming you run this on a standard-compliant DBMS. BTW, this is one the main differences between CHARs and VARCHARs.

edited Oct 01 '09 at 16:41

answered Oct 01 '09 at 16:31

Milan Babuškov

59,775
49
126
179

why would the second example yield 0 rows? These two statememts look equivalent to me given your test data – Mike Marshall Oct 01 '09 at 16:33
3

because 'test' is stored as 'test' + 4 spaces. SQL standard says that LIKE should not match that, while = should. For VARCHAR columns there is no difference. – Milan Babuškov Oct 01 '09 at 16:35
11

Yet another reason for never using char data type unless you will always have that exact number of characters. – HLGEM Oct 01 '09 at 17:26
4

@HLGEM -- who needed another reason? – tvanfosson Oct 02 '09 at 10:43
2

What RDBMSs does this apply to? Doesn't make any difference in SQL Server – Martin Smith Jan 02 '11 at 00:19
1

One extra case that may help understanding one of the differences : http://stackoverflow.com/questions/10715609/sql-server-automatically-strips-trailing-whitespace – user2173353 Jun 01 '16 at 10:57

score 13 · Answer 3 · edited May 23 '17 at 12:17

13

In that case, there is no difference that would come up in the results. However, it uses a different method for comparision, and the "LIKE" would be much slower.

Check out this for examples of LIKE : http://www.techonthenet.com/sql/like.php

In this case, you still want to use the equals.

Update: Note that there is a crucial difference when it comes to CHAR type columns in which the results will be different. See this answer for more details. When using VARCHAR (presumably the norm), the above are equivalent and equals is to be preferred.

edited May 23 '17 at 12:17

Community

1
1

answered Oct 01 '09 at 16:27

Erich

3,946
1
22
21

1

Potentially in a stored procedure, however in testing in SQL Server through dynamic SQL, I've noticed it does not optimize this. I've also run a test on Oracle, and it doesn't seem to optimize Dynamic SQL. – Erich Oct 01 '09 at 16:34
1

I find it amazing how wrong answers often get accepted on SO. :( – Milan Babuškov Oct 01 '09 at 16:36
In what manner would you say that my answer is wrong? In this situation, LIKE and '=' have identical functionality. He really means '=', and thus should. I also pointed out a bit of documentation on how LIKE should be used... Pretty sure I answered the question as much as everyone else. – Erich Oct 01 '09 at 16:42
1

See my answer below, and read up on SQL specs. There is an important difference if you use CHAR columns. – Milan Babuškov Oct 01 '09 at 16:44
Ah, I knew of that difference, however I hadn't run into that in a while. I made the assumption he was using VARCHAR, since I (and my company) tend to use it any time the data doesn't have a fixed length. – Erich Oct 01 '09 at 16:46

score 5 · Answer 4 · edited Jun 17 '11 at 14:58

5

create table A (id int,name varchar(30))

insert into A values(4,'subhash')

Use the trailing whitespace to search the name field:

select * from A where name='Subhash '
--Yields 1 row
select * from A where name like 'Subhash '
--Yields 0 row

edited Jun 17 '11 at 14:58

DarthJDG

16,511
11
49
56

answered Jun 17 '11 at 05:50

subhash

51
1
1

score 4 · Answer 5 · answered Oct 01 '09 at 16:27

4

LIKE allows wildcards like % (any number of characters here) and _ (one character here).

SELECT * FROM users WHERE username LIKE 'joe%'

Selects all usernames starting with joe.

answered Oct 01 '09 at 16:27

T.J. Crowder

1,031,962
187
1,923
1,875

Larsenal · Answer 6 · 2009-10-01T17:28:01.627

3

LIKE searches for a pattern.

/* Returns all users whose username starts with "d" */
SELECT * FROM users WHERE username LIKE 'd%'

/* Returns all users whose username contains "dav" */
SELECT * FROM users WHERE username LIKE '%dav%'

edited Oct 01 '09 at 17:28

answered Oct 01 '09 at 16:29

Larsenal

49,878
43
152
220

score 2 · Answer 7 · answered Oct 01 '09 at 16:27

2

That will give you the same result. However, LIKE allows wildcards, for example...

SELECT * FROM users WHERE username LIKE 'davy%'

The only syntax problem was double quotes instead of single quotes

answered Oct 01 '09 at 16:27

Richard

29,854
11
77
120

score 1 · Answer 8 · answered Oct 01 '09 at 16:26

1

LIKE supports wildcards. Usually it uses the % or _ character for the wildcard.

Using the LIKE operator with no wildcards is the same as using the = operator.

answered Oct 01 '09 at 16:26

auujay

578
5
19

score 1 · Answer 9 · edited Jun 20 '20 at 09:12

1

The LIKE condition allows you to use wildcards:

SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';

See more examples.

and Equals = is used for equality matching.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 01 '09 at 16:28

Daniel May

8,156
1
33
43

score 1 · Answer 10 · edited Jun 17 '11 at 15:48

As far as I know, there is no difference but a time cost to the two selects you wrote. Usually one uses LIKE together with %, meaning 'any string'. I think there's also a character that can be used with LIKE for 'any character', not sure what that is without googling.

But as your two selects go, the only difference I see is a different run time, since LIKE is used in a regexp-sort-of-fashion.

score 1 · Answer 11 · edited Jun 17 '11 at 15:48

1

Like is pattern matching operator and = is exact matching operator. i.e. where name like W% it means start with W and after that one or more characters and = i.e. where name ='James' this is exact matching

edited Jun 17 '11 at 15:48

axel22

32,045
9
125
137

answered Nov 29 '09 at 18:30

P Sharma

2,638
11
31
35

score 0 · Answer 12 · answered Oct 01 '09 at 16:27

Equals '=' is just for equality. On the other hand, LIKE supports SQL wildcard matching.

So, with LIKE you can do name like '%jones' to get all the names ending in jones. With LIKE, the percent '%' character is anything, length zero or more, and the underscore character, '_', is any one character.

score 0 · Answer 13 · edited Jan 05 '13 at 12:42

0

Like gets you to work with wild card operators, you may use it in your case for like 'davyjon%' to get all the results starting with davyjon, and to get the exact you may place 'davyjones' and you may also use = in this case

edited Jan 05 '13 at 12:42

Andriy M

76,112
17
94
154

answered Nov 29 '09 at 18:03

SQL Like

1
1

What's the difference between "LIKE" and "=" in SQL?

13 Answers13

Linked