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

Solution 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

Solution 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.

Solution 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.