SQL Between: A Complete Guide

The SQL BETWEEN keyword retrieves values that fall within a particular set of values. It is often used to retrieve values between two dates or numbers. This keyword is used in WHERE clauses. The syntax for this keyword is: “BETWEEN min AND max;”.

Often, you’ll want to gather information from a database that exists between two values. You can use the BETWEEN operator to do this.

The SQL BETWEEN keyword allows you to define a range for a query and retrieve all values within that range. For example, say you have a database of employee salaries. You can use BETWEEN to generate a list of all employees who earn between $50,000 and $60,000.

In this tutorial, we look at how to use BETWEEN in SQL and discuss some of its practical applications.

SQL BETWEEN Keyword

The SQL BETWEEN keyword lets you select values in a particular range. The BETWEEN keyword is specified in the WHERE section of an SQL statement. You need to use the AND keyword to specify the two values in your range.

Here’s the syntax for an SQL BETWEEN condition:

SELECT column_name FROM table_name
WHERE column_name
BETWEEN minimum_value AND maximum_value;

As you can see, the BETWEEN keyword comes after the WHERE statement. We use an AND keyword to separate the minimum and maximum values.

You could retrieve values between two dates or numbers with the BETWEEN keyword. For instance, say you want to find out who was hired between two dates. You could do this with the BETWEEN keyword.

BETWEEN works on strings, too. In this case, BETWEEN finds strings that fall between your comparison. For instance, you could find all the students whose grades were between A and C in a database.

This keyword works on SELECT, INSERT, UPDATE, and DELETE statements.

SQL BETWEEN Example

Consider the following table of employees:

Our table has two columns and seven rows. We created this table using the SQL CREATE TABLE and SQL INSERT commands.

Let’s say that we want to retrieve the names and salaries of all employees who earn between $35,000 and $55,000 per year. We can use the following query to retrieve this data:

SELECT name, salary FROM employees
WHERE salary 
BETWEEN 35000 AND 55000;

Our SQL server returns the following:

(4 rows)

You can also use the BETWEEN operator to search for information between certain dates.

Say we want to retrieve the names and hire dates of all employees brought on between January 1, 2009 and December 31, 2011. We can also do this using a BETWEEN statement:

SELECT name, hired_date FROM employees
WHERE hired_date
BETWEEN CAST('2009-01-01' AS DATE) AND CAST('2011-12-31' AS DATE);

Here is the result of our query:

(4 rows)

Let’s use an example of the BETWEEN operator with a string. We want to find out the names of all employees whose names start with any letter between A and F in the alphabet. We can do this again using a BETWEEN statement:

SELECT name FROM employees
WHERE name
BETWEEN 'A' AND 'F';

Our query returns the following:

(2 rows)

Conclusion

The SQL BETWEEN operator retrieves records whose column values that lie within a specific range. You can use this operator to find values between two strings, dates, and numbers.

As a challenge, write a query that would retrieve the IDs of all employees in the range of 300 and 400. The table is called “employees” and each employee ID is stored in the column “id”.

Do you want to learn more about SQL? Check out our How to Learn SQL guide. You’ll find a list of top courses and learning resources to help you build your knowledge of the SQL database language.