How can you use the BETWEEN operator to filter data in SQL queries?

4 How to use the BETWEEN operator with dates?

Another common use of the BETWEEN operator is to filter data based on date values, such as birthdays, order dates, or expiration dates. You can use the BETWEEN operator with any date or datetime data type, such as date, timestamp, or interval. For example, if you want to find the employees who were hired between January 1, 2020 and December 31, 2020, you can use the following query:

SELECT employee_id, first_name, last_name, hire_date FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';        

This will return the employee_id, first_name, last_name, and hire_date of the employees who were hired in 2020. Note that you have to use single quotes to enclose the date values, and follow the format of the date or datetime data type in your database.

5 How to use the BETWEEN operator with strings?

You can also use the BETWEEN operator to filter data based on string values, such as names, categories, or codes. You can use the BETWEEN operator with any character or text data type, such as char, varchar, or text. For example, if you want to find the products that have a category name between 'Books' and 'Games', you can use the following query:

SELECT product_id, product_name, category_name FROM products
WHERE category_name BETWEEN 'Books' AND 'Games';        

This will return the product_id, product_name, and category_name of the products that have a category name that starts with 'B', 'C', 'D', 'E', 'F', or 'G'. Note that the BETWEEN operator compares the string values based on their alphabetical order, and is case-sensitive.

6 How to use the NOT BETWEEN operator?

Sometimes, you may want to filter data that does not fall within a certain range. In that case, you can use the NOT BETWEEN operator, which is the opposite of the BETWEEN operator. The syntax of the NOT BETWEEN operator is: column_name NOT BETWEEN low_value AND high_value The NOT BETWEEN operator excludes the low_value and the high_value from the range. For example, if you want to find the customers who have not placed orders with a total amount between $100 and $500, you can use the following query:

SELECT customer_id, order_id, total_amount FROM orders
WHERE total_amount NOT BETWEEN 100 AND 500;        

This will return the customer_id, order_id, and total_amount of the orders that have a total amount less than $100 or greater than $500.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?