BETWEEN Operator

In SQL, the BETWEEN operator is used to filter rows within a specified range of values for a given column. It checks if a column's value lies within a specific range, inclusive of both the start and end values.

The basic syntax of the BETWEEN operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here's an example to illustrate its usage:

Suppose we have a table named "Products" with a column named Price. We want to retrieve all products with a price between $10 and $50.

SELECT ProductID, ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 50;

In this example, the BETWEEN operator is used to filter rows where the Price column's value lies within the range of 10 and 50, inclusive. The result will include all products with prices between $10 and $50.

The BETWEEN operator can be used with various data types, such as numeric, date, and character types. It's essential to ensure that the data type of the column and the range values match correctly to avoid unexpected results.

You can also use the BETWEEN operator with dates to filter rows within a specific date range. For example:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-06-30';

In this example, the BETWEEN operator filters rows with an OrderDate falling within the first half of the year 2023.

It's important to remember that the BETWEEN operator includes both the start and end values in the result set. If you need to exclude either the start or end value from the range, you can use the > (greater than) or < (less than) operators in combination with AND.

For example, to retrieve products with prices greater than $10 and less than $50:

This will exclude products with prices equal to $10 or $50 from the result set.