Filtering: LIKE, IN, BETWEEN, AND, OR, NOT

Introduction

When writing SQL queries, having an understanding of the concept of filtering is very important because oftentimes, we want to get only specific rows or columns from a table rather than the whole table itself. Most of the time, the filtering is done with the WHERE keyword. In order to get a basic understanding of what this keyword does, please check out this AlgoDaily tutorial.

Back to our tutorial; we will use the WHERE statement in all examples, together with some other very important keywords: LIKE, IN, BETWEEN, AND, OR, and NOT. All the examples we will go through in this lesson will be based on the table shown below. The name of the table is Person.

Like

The LIKE operator is used for finding specific patterns in the columns. Both the percent sign (%) and the underscore sign (_) might act as wildcards, with the first one representing zero, one, or multiple characters, and the latter one representing exclusively one character. These two signs can also be used in combinations.

If we’d like to return only those rows where the order, we’d write the following query:

1SELECT * 
2FROM Person
3WHERE OrderCode LIKE ‘11%’;

The resulting table would look like this:

In the table below, we show some examples of how LIKE can be used with ‘%’ and ‘_’ wildcards:

And

The AND operator can be used in SQL for getting all rows that satisfy all of the conditions that are separated by AND. That means that we can have more than one condition on which we should do the filtering. Imagine we want to get all customers living in the USA that are male. In such a case, we’d write the following query:

1SELECT * 
2FROM Person
3WHERE Country == ‘USA’ AND Sex == Male;

This is the resulting table after running the query written above:

Or

The OR operator can be used in SQL for getting all rows that satisfy at least one of the conditions that are separated by OR. With this operator, filtering can be conducted on more than one condition.

Let’s say we want to find all people that live in either the United States of America, Spain, or Denmark. Then, we’d have to write the following query:

1SELECT * 
2FROM Person
3WHERE Country == ‘USA’ OR Country == ‘Spain’ OR Country == ‘Denmark’;

The resulting table would look like this:

In

We can use the IN statement to find the values that belong to a specified set. Since this operator allows us to specify more than one value, we can say that it is a shorthand for several OR statements.

Let us prove that by getting the same resulting table as we did by using the OR operators:

1SELECT * 
2FROM Person
3WHERE Country IN (‘USA’, ‘Spain’, ‘Denmark’);

The resulting table is the same as in the ‘OR’ example above.

Not

The SQL syntax supports the NOT operator, which returns the rows for which the condition is not true. Therefore, looking at the example above, imagine we'd like to return all people that are not from the United States of America, Spain, and Denmark. We can do that by only putting in the NOT keyword, and the query would look like this:

1SELECT * 
2FROM Person
3WHERE Country NOT IN (‘USA’, ‘Spain’, ‘Denmark’);

The resulting table would be:

Between

The BETWEEN operator is used to filter out only rows belonging to a specific range. These values may be strings, numbers, or dates. When using the between operator, we specify the beginning and ending values and these values are also included in the results (the operator is inclusive).

Let us write a query that would get all information about the orders that were made in 2021:

1SELECT * 
2FROM Person
3WHERE DateOfOrder BETWEEN ‘2021-01-01’ AND ‘2021-12-31’

After running the query, we get the following table:

One Pager Cheat Sheet

• This tutorial introduces the WHERE keyword and other important keywords, LIKE, IN, BETWEEN, AND, OR, and NOT, to help filter data efficiently in SQL queries utilizing the Person table.
• The LIKE operator is used to find specific patterns in the columns, using percent and underscore signs as wildcards.
• The LIKE operator with % wildcard is used to retrieve all rows from the Person table where the Name column ends with "a".
• The AND operator can be used to filter data by specifying multiple conditions in a SQL query, for example to get all customers living in the USA which are male.
• The OR operator can be used in SQL to filter records that match at least one of multiple conditions.
• The IN statement allows us to find the values that belong to a specified set, and is a shorthand for multiple OR statements.
• The NOT IN operator allows us to return all rows not satisfying a given condition.
• By combining the AND, OR, and NOT keywords through the use of parentheses, it is possible to create complex conditional logic in a single query.
• The BETWEEN operator is used to filter out rows between two specified values (inclusive) from a table.