{"id":12385,"date":"2021-01-11T11:23:00","date_gmt":"2021-01-11T19:23:00","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=12385"},"modified":"2023-12-01T04:07:58","modified_gmt":"2023-12-01T12:07:58","slug":"sql-between","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-between\/","title":{"rendered":"SQL Between: A Complete Guide"},"content":{"rendered":"\n

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;”.<\/em><\/p>\n\n\n\n

Often, you\u2019ll want to gather information from a database that exists between two values. You can use the BETWEEN<\/em> operator to do this.<\/p>\n\n\n\n

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<\/em> to generate a list of all employees who earn between $50,000 and $60,000.<\/p>\n\n\n\n

In this tutorial, we look at how to use BETWEEN<\/em> in SQL<\/em> and discuss some of its practical applications.<\/p>\n\n\n\n

SQL BETWEEN Keyword<\/h2>\n\n\n\n

The SQL BETWEEN<\/em> keyword lets you select values in a particular range. The BETWEEN<\/em> 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.<\/p>\n\n\n\n

Here\u2019s the syntax for an SQL BETWEEN<\/em> condition:<\/p>\n\n\n\n

SELECT column_name FROM table_name\nWHERE column_name\nBETWEEN minimum_value AND maximum_value;<\/pre><\/div>\n\n\n\n
As you can see, the BETWEEN<\/em> keyword comes after the WHERE statement. We use an AND keyword to separate the minimum and maximum values.<\/p>\n\n\n\n
You could retrieve values between two dates or numbers with the BETWEEN<\/em> keyword. For instance, say you want to find out who was hired between two dates. You could do this with the BETWEEN<\/em> keyword.<\/p>\n\n\n\n
BETWEEN<\/em> 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.<\/p>\n\n\n\n
This keyword works on SELECT, INSERT, UPDATE, and DELETE statements.<\/p>\n\n\n\n
SQL BETWEEN Example<\/h2>\n\n\n\n
Consider the following table of employees:<\/p>\n\n\n\n
name<\/strong><\/td>salary<\/strong><\/td><\/tr>
Geoff<\/td>38000<\/td><\/tr>
Mike<\/td>32000<\/td><\/tr>
Emma<\/td>50000<\/td><\/tr>
Jonah<\/td>50000<\/td><\/tr>
Luke<\/td>32000<\/td><\/tr>
Alexis<\/td>33000<\/td><\/tr>
Hannah<\/td>30000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Our table has two columns and seven rows. We created this table using the SQL CREATE TABLE<\/a> and SQL INSERT<\/a> commands.<\/p>\n\n\n\n
Let\u2019s 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:<\/p>\n\n\n\n
SELECT name, salary FROM employees\nWHERE salary \nBETWEEN 35000 AND 55000;<\/pre><\/div>\n\n\n\n
Our SQL server returns the following:<\/p>\n\n\n\n
name<\/td>salary<\/td><\/tr>
Geoff<\/td>38000<\/td><\/tr>
Mike<\/td>32000<\/td><\/tr>
Emma<\/td>50000<\/td><\/tr>
Jonah<\/td>50000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(4 rows)<\/p>\n\n\n\n
You can also use the BETWEEN<\/em> operator to search for information between certain dates.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
SELECT name, hired_date FROM employees\nWHERE hired_date\nBETWEEN CAST('2009-01-01' AS DATE) AND CAST('2011-12-31' AS DATE);<\/pre><\/div>\n\n\n\n
Here is the result of our query: <\/p>\n\n\n\n
name<\/td>hire date<\/td><\/tr>
Luke<\/td>2009-12-03<\/td><\/tr>
Emma<\/td>2010-03-19<\/td><\/tr>
Mike<\/td>2010-03-19<\/td><\/tr>
Hannah<\/td>2011-09-30<\/td><\/tr>
Jonah<\/td>2010-07-23<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(4 rows)<\/p>\n\n\n\n
Let’s use an example of the BETWEEN<\/em> operator with a string. We want to find out the names of all employees whose names start with any letter between A<\/em> and F<\/em> in the alphabet. We can do this again using a BETWEEN statement:<\/p>\n\n\n\n
SELECT name FROM employees\nWHERE name\nBETWEEN 'A' AND 'F';<\/pre><\/div>\n\n\n\n
Our query returns the following: <\/p>\n\n\n\n
name<\/td><\/tr>
Emma<\/td><\/tr>
Alexis<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(2 rows)<\/p>\n\n\n\n