Using 'name LIKE ...' instead of 'name = ...' in SQL

Language Help SQL
1

I’m wondering if anyone knows the reason for the LIKE operation in SQL. Specifically, why can’t you just use the = operator.

For instance, when creating a query, why do I have to use name LIKE 'A%' to select all the names that begin with ‘A’? Why can’t I just use name = 'A%'?

LIKE just seems to be redundant operator.

Any explanation would be appreciated. Thanks!

1 Like
2

Hi @addisonscott,

In SQL, the LIKE operator allows certain wildcard characters to be used in a match, while the = operator does not. For example, with LIKE, the % wildcard can match zero or more of any characters. Accordingly, '%dollar%' will match any string that contains the substring, 'dollar'.

In the The Metropolitan Museum of Art project, this …

SELECT title, date, medium FROM met
WHERE title LIKE '%dollar%';

… SELECTs this data …

|title|date|medium|
|---|---|---|
|One-dollar Liberty Head Coin|1853|Gold|
|Ten-dollar Liberty Head Coin|1901|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-Half Dollar Coin|1909–27|Gold|
|Two-and-a-half-dollar Indian Head Coin|1912|Gold|
|Two-and-a-half-dollar Liberty Head Coin|1907|Gold|
|Twenty-dollar Liberty Head Coin|1876|Gold|
|Five-dollar Indian Head Coin|1910|Gold|
|Five-dollar Liberty Head Coin|1907|Gold|

If we use = instead of LIKE in the statement above, no records are SELECTed.

Edited on October 15, 2018 to correct a typographical error

2 Likes
3

Thanks for the reply, @appylpye! I understand that = does not interpret wildcards and LIKE does interpret wildcards. I guess the deeper question I’m asking though is “Why?”.

Is there a specific reason that these two separate operators need to exist in SQL? For people who use SQL every day, is it there a common need to perform a query that looks for data with % or _ in it, and NOT interpret them as wildcards?

Maybe this will become more clear to me as I get deeper into the SQL lessons :slight_smile:

4

You can get by with using the LIKE operator without wildcards instead of = when you are looking for an exact match. However, including = among the comparison operators available in SQL may allow for more consistent style when using AND and OR to form complex comparisons.

Suppose you wanted to see all records in a table named Boxes where height is less than or equal to 10 and width is exactly 12. You could do this …

SELECT * FROM Boxes
WHERE height <= 10 AND width LIKE 12;

… or this …

SELECT * FROM Boxes
WHERE height <= 10 AND width = 12;

However, the style of the latter seems nicer and easier to read.

1 Like