The next query on our list is
LIKE. This is used for finding patterns when we don’t know the exact data.
# Table of Contents
Query 1: Using LIKE
SELECT title, description FROM film WHERE description LIKE ‘%thrilling%’
Q1. How can I find a movie that’s thrilling? Finding thrillers through the film description.
Let’s say you’re browsing the sakila database and you want to look up some information about the cities in the database. You can start with:
SELECT * FROM `sakila`.`city`;
If you wanted to just look up a specific city, like New Delhi, you could add
WHERE city=‘New Delhi’ to find information on that city.
But let’s say you don’t quite remember the name of the city. You know it has the word “New” in it, but you’re not sure of the second half. In cases like this, we need the query
📌LIKE: find a specific pattern in a column
So we want to find Thrillers. If there was a column named “GENRE” we could just use the
WHERE query. But in this database, the genre of the movie is in the description column. So we need to get creative. Let’s change “Thriller” into “thrilling” and search the descriptions. Try writing “title, description” after
SELECT and ‘film’ after
FROM, like this:
SELECT title, description FROM film WHERE description LIKE thrilling;
⛔️STOP! You’re going to get an error. This is because we didn’t put the single quotes (
‘ ’ ) around thrilling. But even when we do, you won’t get any results. Why, you ask?
🔑 In SQL, the percentage sign (
% ) and underscore (
_ ) are called Wildcard characters.
% is used when the number of characters/words that surround the term you’re searching doesn’t matter.
_ is for selecting something exact, and you want an exact match.
For this example, we need
%. So let’s try it again:
SELECT title, description FROM film WHERE description LIKE ‘%thrilling%’;
Nice! Here are some examples of the wildcards.
ex%→ example (Start with ‘ex’)
LIKE ‘% whatever’:
%ex→ complex (End with ‘ex’)
%ex%→ lexicon (Include ‘ex’)
‘ex__’→ exit (Start with ‘ex’)
LIKE checks every data for the ‘whatever’ that you’re searching for, so it can take a lot of time to process when dealing with a large amount of data. If the amount of data is heavy, it’s better to avoid the
# Practice Time
📰 Today’s example scenario requires you to apply previously learned queries in various tables. We want to find the addresses of all our customers that live in South Korea. We’ll be using three different tables to do this: country, city, and address. We’ve mostly been using the film table until now, so it might be hard to find data in other tables. Let’s try this query first:
SELECT country_id, country FROM country;
First, let’s find the country_id of South Korea in the country table. We don’t want to scroll through all this new information to find it, so let’s try a query. A good place to start is to search for a pattern, so let’s try plugging in
WHERE country LIKE ‘%korea%’ because there may be many values that contain the word ‘Korea’.
SELECT country_id, country FROM country WHERE country LIKE ‘%korea%’;
Once you run this query you’ll see there are two results: South Korea and North Korea. The one we want is South Korea, so make note of the ID.
Now that we have the ID number of South Korea (
country_id=86) we can go to the next step. This time we will look at the cities of this country in the city table. Add city to the
SELECT query and change the table name for the
FROM query to city. We’re also going to keep using the
WHERE query, so go ahead and plug in the country ID at the end.
SELECT city_id, city, country_id FROM city WHERE country_id=86;
In the results, you’ll see a total of 5 values. You can get the city name and number (113, 268, 357, 539, 553) of each city from this new query result.
Now let’s look at the addresses that we want to find. We want to view the address ID, address, district and city ID so designate that in your
SELECT query. We’ve also moved over to the address table now, so make sure to change that in your
In our first step we found the country code for South Korea (86) and we used that in the second step to find city ID numbers. So in the address table, you only need to search for the addresses corresponding to these city IDs. An easy way to use them is to combine
IN, as we just covered in this tutorial.
SELECT address_id, address, district, city_id FROM address WHERE city_id IN (113, 268, 357, 539, 553);
There! Now we have the addresses of all customers in South Korea. Below we’ve provided an image of all these queries together. You can run them at the same time to compare the information.
SELECT country_id, country FROM country WHERE country LIKE ‘%korea%’; SELECT city_id, city, country_id FROM city WHERE country_id=86; SELECT address_id, address, district, city_id FROM address WHERE city_id IN (113, 268, 357, 539, 553);