# What's the Difference Between "LIKE" and "=" in SQL?

sql

best-practices

performance

join

byAnton Shumikhin·Dec 11, 2024

LIKE operates with wildcards (% for multiple characters and _ for a single character) for approximate matching, whereas = searches for exact equality. Here's an instance with LIKE:

-- Searching for pizza lovers 😋🍕
SELECT * FROM users WHERE fav_food LIKE 'Pizza%';

This will get us all users whose favorite food starts with 'Pizza' (e.g., 'Pizza', 'Pizza Margherita'). Using =:

-- Sam loves 'Pizza' only, not 'Pizza Margherita'
SELECT * FROM users WHERE fav_food = 'Pizza';

You'll fetch just those folks who only love 'Pizza', not anything more and definitely nothing less.

Head-to-Head: Operators Breakdown

In SQL, using LIKE versus = very much depends on the specific requirement of your search:

When you're looking for a needle in a haystack, use = for exact and precise matches.
On the other hand, LIKE is your trusted friend for scavenger hunts, when approximations or patterns are more than good enough.

The Power of Wildcards with LIKE

With LIKE, two wildcard characters - % and _ - enable creative querying:

%: Masks any possible concoction of characters
_: Symbolizes a placeholder for a solitary character

White Spaces Woes

In a CHAR column, trailing spaces treat LIKE and = differently:

='abc': Indifferent to trailing spaces
LIKE 'abc ': Considers trailing spaces in the match criteria

Performance: LIKE vs =

The operator you choose can also have performance implications:

= is faster, since exact matches require less computational effort.
However, using LIKE (especially with leading wildcards) could potentially put your database on a slower track.

Decoding Patterns in Text

Embrace LIKE to uncover patterns like email domains, username prefixes, or file extensions:

-- Interns don't get to handle '@importantstuff.com' mails!
SELECT * FROM emails WHERE recipient LIKE '%@internsareimportant.com'; 

-- Find all those audacious JPEG lovers
SELECT * FROM users WHERE fav_file_format LIKE '%.jpeg';

Case Sensitivity: No Case of The Mondays!

The case sensitivity in LIKE and = is subject to the SQL dialect:

LIKE in SQL Server: pretty chill and case-insensitive by default.
LIKE in PostgreSQL: sensitive and prefers it when the case matches exactly, unless you introduce ILIKE.

LIKE for Wild Pattern Hunts

LIKE shines when the exact order or format isn't known:

-- '555' might be the area code, but who knows the extension!  
SELECT * FROM contacts WHERE phone LIKE '(555)%'; 

-- Do we have product codes that begined with 'PC-8'? Wait! That doesn't sound right....
SELECT * FROM products WHERE code LIKE 'PC-8__';

The Final Call: Like or Equals?

When you have to choose your operator:

When precision and exact matching is needed, the = won't let you down.
When you're on the hunt for similar patterns or approximate matches, then LIKE is your best bet.

Conclusion

Choosing between LIKE and = in SQL hinges on your search's granularity and context. By understanding your dataset and requirements, you can pick the most efficient and effective operator. Practice and experiment to see the magic unfold and become an SQL wizard! Give your vote if this answer hit the spot. Happy query crafting!👩‍💻