WikiGalaxy
SQL BETWEEN Operator
Introduction to SQL BETWEEN
Understanding BETWEEN:
The SQL BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text, or dates.
Syntax:
The basic syntax of the BETWEEN operator is: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM Employees WHERE Age BETWEEN 30 AND 40;
Explanation:
This query selects all employees whose age is between 30 and 40, inclusive.
Console Output:
Employee records with ages 30 to 40
Using BETWEEN with Dates
Date Range Filtering:
BETWEEN can also be used to filter records within a specific date range.
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';
Explanation:
This query retrieves all orders placed in the year 2023.
Console Output:
Orders from 2023
BETWEEN with Text Values
Alphabetical Range:
BETWEEN can be used to filter text values within a specified alphabetical range.
SELECT * FROM Customers WHERE LastName BETWEEN 'A' AND 'M';
Explanation:
This query selects customers whose last names start with letters A through M.
Console Output:
Customer records with last names A-M
Inclusive Nature of BETWEEN
Inclusion of Boundaries:
The BETWEEN operator includes both the starting and ending values in the range.
SELECT * FROM Products WHERE Price BETWEEN 50 AND 100;
Explanation:
This query fetches products priced between $50 and $100, including both $50 and $100.
Console Output:
Products priced $50 to $100
Combining BETWEEN with AND
Multiple Conditions:
BETWEEN can be combined with the AND operator to filter results based on multiple criteria.
SELECT * FROM Sales WHERE SaleDate BETWEEN '2023-01-01' AND '2023-06-30' AND Amount BETWEEN 500 AND 1000;
Explanation:
This query selects sales made in the first half of 2023 with amounts between $500 and $1000.
Console Output:
Sales from Jan to Jun 2023, $500-$1000
BETWEEN with NOT Operator
Excluding Ranges:
The NOT operator can be used with BETWEEN to exclude a range of values.
SELECT * FROM Inventory WHERE StockLevel NOT BETWEEN 10 AND 20;
Explanation:
This query retrieves inventory items with stock levels outside the range of 10 to 20.
Console Output:
Inventory stock levels not 10-20
BETWEEN with Calculated Columns
Using Expressions:
BETWEEN can be applied to calculated columns or expressions within a query.
SELECT * FROM Products WHERE (Price * Quantity) BETWEEN 1000 AND 5000;
Explanation:
This query selects products with total value (price times quantity) between $1000 and $5000.
Console Output:
Products valued $1000 to $5000
Performance Considerations
Optimization Tips:
When using BETWEEN, ensure that the columns involved are indexed to optimize query performance.
-- Ensure indexing on columns used with BETWEEN
CREATE INDEX idx_age ON Employees (Age);
Explanation:
Indexing the 'Age' column can significantly improve the performance of queries using BETWEEN on this column.
Console Output:
Index created on Age column
