Skip to main content

IN and BETWEEN

Premium

In this lesson, we'll explore the use of the IN and BETWEEN operators in SQL. These operators allow us to define conditions that match a value against a set of values or a range of values. They are widely used in SQL to filter or sort data based on certain criteria.

The IN operator

The IN operator is used to match a value against a set of values. The syntax is:

SQL
SELECT columns
FROM table_name
WHERE column_name IN (value1, value2, ...);

Let's say we have a courses table and want to retrieve multiple courses by a given column. We could write the query with a series of conditions on the same column like

SQL
WHERE category = 'SQL' OR category = 'Data Science' OR …

Or we can choose to write the same query more succinctly with the IN operator:

SQL
WHERE category IN ('SQL', 'Data Science')

Try it yourself

Let's put the IN operator to use. Given a table of customers, write a query that returns rows where the last name is in the list "Parker", "Wayne".

The BETWEEN operator

The BETWEEN operator is used to match a value against a whole range of values, marked by a beginning and end value, rather than a discrete set. The syntax is:

SQL
SELECT columns
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

In the context of numerical values, the BETWEEN operator can be used to select values within a certain range. It is inclusive, meaning that both the lower and upper bounds specified in the BETWEEN clause are included in the result set. For example, to select all courses with a rating between 4 and 5, you would write the following query:

SQL
SELECT *
FROM courses
WHERE rating BETWEEN 4 AND 5;

When used with dates, the BETWEEN operator works in the same way, allowing you to select dates within a specified range. For example, to select all lessons that were created between January 1st and January 31st, you could write the following query:

SQL
SELECT *
FROM lessons
WHERE created_at BETWEEN '2022-01-01' AND '2022-01-31';

Note that while this interval does include the righthand date, it does not automatically extend to the "end" of the date. If you want an interval that extends to the end of a given day or month, it is often more precise to write the range as a closed-open interval, for example:

SQL
WHERE created_at >= '2022-01-01' AND created_at < '2022-07-01';

For text values, the BETWEEN operator works lexicographically, meaning that it compares the ASCII values of the characters in the strings. In this case, it's also inclusive, allowing you to select strings within a certain range. For example, to select all courses whose names are between 'A' and 'M', you could write the following query:

SQL
SELECT *
FROM courses
WHERE name BETWEEN 'A' AND 'M';

It's worth noting that BETWEEN is similar to using < and > expressions to specify a range. For example, the following query is equivalent to the previous example:

SQL
SELECT *
FROM courses
WHERE name >= 'A' AND name <= 'M';

Try it yourself

Now let's put the BETWEEN operator to use. Given the same table of customers, write a query that returns rows that were created in January, 2022 based on the created_at column.

In this lesson, we learned about the IN and BETWEEN operators and how they can be used to select values within a specified range. These operators are useful when you want to filter your results based on multiple conditions, and can be used with numerical values, dates, text values, and arrays. In the following lesson, we’ll learn about aggregate functions in SQL and how they can be used to perform calculations on large sets of data.

Next Lesson