SQL Operators (LIKE, IN, BETWEEN)

In this lesson you are going to learn about SQL operators LIKE, IN and BETWEEN.

I have called them SQL operators, because as opposed to the ones we saw in the previous lesson, these ones are specific to SQL.

Besides LIKE, IN and BETWEEN, IS NULL is part of this group too, but you already know it very well, from the lesson about NULLs, right?

So, let’s talk about LIKE.

LIKE operator

This operator is used when you want to compare character data but don’t want to check for an exact match, but you only want to see if the string matches a certain pattern.

To define your patterns you can use 2 special characters, which are known as “pattern matching characters”.

The first one is the percentage sign (%), which matches any string of any length, and the second one is the underscore (_), which matches a single character.

Let’s see it with an example.  I’m going to use my products table again:

If I want to list all products whose name starts with an “A”, I can write a query like this one:

SELECT *
FROM products
WHERE product_name LIKE 'A%';

This will match any product whose name has an “A” at the beginning.  The percentage sign matches any string of any length, including blank spaces.  It even matches an empty string, so if we had a product whose name was just the letter “A”, that row would be returned, because the percentage sign matches strings of 0 or more characters.

The percentage sign can appear in any part of the pattern, I mean, it could be at the beginning, in the middle, or at the end. And you can use more than one percentage sign in the same pattern.

For example, if I want to list all products whose name includes an “o” and an “n”, I would need something like this:

SELECT *
FROM products
WHERE product_name LIKE '%o%n%';

I put the first one there because I don’t need the “o” to be at the beginning.  It can, but that is not a requirement.

The second one is there because I don’t need the “o” and the “n” to be next to each other, they can, but they can also have some other characters between them.

And I added the third one because I don’t need the “n” to be at the end of the string.

Okay, now let’s look at the underscore.

The underscore matches a single character and it is very useful, but you know what is really strange?  At least 80% of developers who I ask about the underscore in the context of SQL, don’t know that it is also a pattern matching character.  Most people only know about the percentage sign.

So, here you have another way to be different, and stand out.

So, if I use a pattern like this ‘___’ it would match any string that is exactly 3 characters long.

And an example that involves the 2 pattern matching characters could be something like this:

SELECT *
FROM products
WHERE PRODUCT_NAME LIKE '_n%';

This will give me all products whose name has an “n” in the second position of the string.

Why?

The underscore at the beginning of the pattern matches the first character, so to match this pattern the product name has to have any character, and only one, at the beginning, before the “n”.  And then I added the percentage sign because I don’t care what comes after the “n”.  It can be a long string, or a single letter, or nothing, it can be anything.

Easy peasy, right?

Okay, your turn!

Here is a short task for you.

Practice Challenge 1

• Write a query to get the list of employees whose name includes the letter “O” 2 times, but not contiguously, so if there was a name “JOHN DOE”, it should be returned, but a row with name “JIM BROOKS” should not.

Expected Output:

• Write a query to get the list of departments whose monthly budget is greater than 15000 and its name includes a “G” or starts with an “H”, sorted by the department id in descending order.

Expected Output:

Okay, now let’s take a look at the IN operator.

IN operator

This operator is used when you want to check whether a column value is included in a list or set of expressions that you specify.

It is very simple, let’s see it with an example:

If I run a query like this one:

SELECT *
FROM products
WHERE price IN (5,10,25);

It gives me all products whose price is 5, products whose price is 10, and products whose price is 25.  It works as if had a condition saying “WHERE price = 5 OR price =10 or price = 25.

So, if the value of the column I’m evaluating is in the list of values I provide, the condition evaluates to true.

And the list inside the parentheses can actually be a list of expressions, and as you remember, expressions can be literals, as in this case, column names, functions, operations, and some other things.

And with the IN operator, instead of a list of expressions, I can even put another query to provide the list of values I want to compare, but that will be covered in the lesson about subqueries.

And to conclude this lesson, we are going to look at the BETWEEN operator, which is also very easy to understand.

BETWEEN operator

BETWEEN is used to check if a column value falls within a specific range.

So, if I write a query like this one:

SELECT *
FROM products
WHERE price BETWEEN 5 and 10;

It gives me the products whose price falls within the range from 5 to 10, and the range includes always the start and end of the range, which in this case are 5 and 10.

So, this condition is equivalent to saying

WHERE price >= 5 AND price <=10

And the values used to define the range don’t need to be literals.  They can be another type of expression too, such as some calculation or function call.

So, here is another short task for you.

Practice Challenge 2

Hey!  Don’t allow yourself to continue to the next lesson if you still have ANY doubt about the concepts that have been covered so far.  Remember:  This is not a race.  Usually there is no reason for wanting to finish the course quickly, so, be sure to give priority to learning, and not to finishing.

Make sure to work on the challenges and not give up until you have a working solution.

And if you need to run more tests to understand how things work, go ahead and do it.  That is how you will learn.

Write a query to list all employees of the Information Technology and Human Resources departments who earn 3000 or more but not more than 5000.  Please include only employees who were born between 1970 and 1990.

Expected Output:

Finished?

Great!!

You are ready to move to the next lesson!