The SQL BETWEEN keyword or operator is used to filter results based on a range of values. It is used in the WHERE clause of a SQL statement to retrieve data from a database table that falls within a specific range. The BETWEEN keyword is typically used with other operators, such as AND and OR, to create more complex queries.
BETWEEN is used in the WHERE clause of SELECT, UPDATE, and DELETE statements. It is typically followed by a field name and two values that define the range. In this article, we will discuss how to use the BETWEEN operator in SQL and provide examples of common usage scenarios.
The SQL BETWEEN Syntax
The basic syntax for the BETWEEN operator is as follows:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
What Data Types Can the SQL BETWEEEN Keyword Filter?
The SQL BETWEEN keyword can be used with different data types, including:
- Numeric data types: This includes INT, BIGINT, FLOAT, DECIMAL, NUMERIC, etc. The BETWEEN keyword can be used to filter results based on a range of numerical values.
- Date and Time data types: This includes DATE, TIME, DATETIME, TIMESTAMP, etc. The BETWEEN keyword can be used to filter results based on a range of date and time values.
- Character and String data types: This includes CHAR, VARCHAR, TEXT, etc. The BETWEEN keyword can be used to filter results based on a range of character and string values.
It's important to note that when using the BETWEEN keyword with character and string data types, the comparison is based on the collation of the strings, meaning that it's case-sensitive or insensitive depending on the collation of the table or column.
For example, when using the BETWEEN keyword to filter string values, the following query would return all rows where the column "name" starts with letters between 'A' and 'C'
SELECT * FROM customers WHERE name BETWEEN 'A' AND 'C';
It's also important to note that the BETWEEN keyword can be replaced by greater than or equal to and less than or equal to operators to achieve the same result.
SELECT * FROM customers WHERE name >= 'A' AND name <= 'C';
SQL BETWEEN operator can be used with different data types including numeric, date and time, character, and string, it's essential to be aware of the collation of the strings when using it with character and string data types, and it can be replaced by other comparison operators.
Common Usage
The BETWEEN operator is used to filter results based on a range of values. For example, if we have a table of products with a price column, we could use the BETWEEN operator to filter only products with prices between $10 and $20:
SELECT * FROM products WHERE price BETWEEN 10 AND 20;
This query would return all rows from the products table where the price column is between 10 and 20.
It's important to note that the BETWEEN operator is inclusive of the values specified. In other words, the query above would return rows where the price is exactly 10 or 20 and any value in between.
Combining With Other Operators
The BETWEEN operator can also be combined with other operators to create more complex queries. For example, we could use the AND operator to filter products with a specific color and price range:
SELECT * FROM products WHERE color = 'red' AND price BETWEEN 10 AND 20;
This query would return all rows from the products table where the color is red and the price is between 10 and 20.
Additionally, we can use the NOT operator to exclude results within a specific range. For example, we could use the NOT BETWEEN operator to filter products with prices outside of the range $10 and $20:
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 20;
This query would return all rows from the products table where the price is not between 10 and 20.
Summary
The SQL BETWEEN operator is a powerful tool for filtering results based on a specific range of values. When used in conjunction with other operators, it can help you create complex queries that return highly targeted results.