IN, LIKE and BETWEEN operators

You already know how to write complex SQL queries and logical expressions. In this topic, we will go further and take a look at three special WHERE operators: BETWEEN, IN, LIKE, that will make your queries more advanced and readable.

BETWEEN operator

The BETWEEN operator in WHERE clause selects values that are within the given range. The range is set by the minimum and maximum values and can include dates, numbers, or text data. The BETWEEN operator is inclusive, so the minimum and maximum values are included in the range.

Let's select products with a price greater than or equal to 8 and less than or equal to 13 from the table products given below.

The query with WHERE BETWEEN will look as follows:

SELECT
    product 
FROM
    products 
WHERE 
    price BETWEEN 8 AND 13; 

The result set of the query above will have three rows:

You can also get the same result set with the query without BETWEEN:

SELECT
    product 
FROM
    products 
WHERE 
    (price >= 8 
     AND price <= 13); 

So you can think of WHERE BETWEEN as a shorthand for >= AND <= in WHERE clause.

To select all the products outside the range, you can use NOT BETWEEN operator:

SELECT
    product 
FROM
    products 
WHERE 
    price NOT BETWEEN 8 AND 13; 

The result set will have three rows excluded now:

IN operator

The IN operator in WHERE clause is a shorthand to multiple OR conditions and allows specifying multiple values.

Let's select products with price equal to 10, 12 or 16 from, the table products from the previous example:

SELECT
    product 
FROM
    products 
WHERE
    price IN (10, 12, 16); 

The result of this query looks like this:

To select products with prices other than 10, 12, or 16 you can negate IN operator using NOT:

SELECT
    product 
FROM
    products 
WHERE
    price NOT IN (10, 12, 16); 

With this query, you will get all the products except milk and pasta:

LIKE operator

The LIKE operator can be used in the WHERE clause to check if a string matches a pattern. To create a pattern, you can use two wildcard match options: % and _.

The % represents any number of characters: zero, one, or more. For example, if you write a pattern %s%, the strings s, toaster, string, and cats will all match this pattern.

The _ represents exactly one character. Thus, if you write a pattern s_, the string so will match this pattern, but the strings s and soap will not.

Let's select all the products with the letter 'a' in any position from our products table:

SELECT
    product 
FROM
    products 
WHERE
    product LIKE '%a%'; 

The query will return this table:

As with BETWEEN and IN operator, you can negate LIKE operator with NOT to get all the products without the a in the product name:

SELECT
    product 
FROM
    products
WHERE
    product NOT LIKE '%a%';

Conclusion

In this topic, you have learned about some special WHERE operators, which allow you to:

• Check if the value is BETWEEN or IN some range.
• Search strings that match the pattern using LIKE operator.

There are also some special operators that you would like to learn, but we will discuss them another time. Now let's practice the knowledge you got here!