SQL BETWEEN Operator
The
SQL BETWEEN clause is used to filter results within a specified range of values.
It is commonly used in conjunction with the SELECT statement in a relational database
management system (RDBMS) to retrieve data that falls within a certain range of
values for a given column.
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator
is inclusive: begin and end values are included.
The syntax for using BETWEEN in SQL is as follows:
SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Where:
·
column_name: The name of
the column you want to filter.
·
table_name: The name of
the table
that contains the column.
·
value1 and value2: The lower and
upper
bounds of
the range, respectively. The values can be numeric, string, or date/time values, depending on the data type of
the column being filtered.
The BETWEEN clause is inclusive, meaning that it includes the values of value1 and value2 in the result set. If you want to exclude the values of value1 and value2, you can use the NOT BETWEEN clause instead.
Here is an example that retrieves all rows from a table named orders where the order_amount falls between $100 and $500:
SELECT order_id, order_date, order_amount
FROM orders
WHERE order_amount BETWEEN 100 AND 500;
This query would return all rows from the orders table where the order_amount is between $100 and $500, inclusive.
BETWEEN Example
The
following SQL statement selects all products with a price between 10 and 20:
SELECT *
FROM Products
WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT *
FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN with IN Example
The following SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
Example
SELECT *
FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
BETWEEN Text Values
Example
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Mozzarella di Giovanni:
Example
SELECT *
FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND
'Mozzarella di Giovanni'
ORDER BY ProductName;
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Chef Antons Cajun Seasoning:
Example
SELECT *
FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND
'Chef Antons Cajun Seasoning'
ORDER BY ProductName;
NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName not between Carnarvon Tigers and Mozzarella di Giovanni:
Example
SELECT *
FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
0 Comments