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"

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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%'

Answer 7

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

Answer 8

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.

Answer 9

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.

Answer 10

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

Answer 11

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.

Answer 12

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.

Answer 13

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

Answer 14

I know this question is too old, but I get many others will try to find out the answer even nowadays. In many books, LIKE may come slower than '-' in most case, except when you want to compare String.