Previous

SQL BETWEEN Operator

The BETWEEN operator in SQL is used to filter values within a specified range. It works with numbers, text, and dates.

 

Basic Syntax of BETWEEN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
  • column_name: The column to compare.
  • value1 and value2: The range of values (inclusive).

 

Key Features of BETWEEN

1. Inclusive Range

The BETWEEN operator includes both boundary values (value1 and value2).

SELECT product_name, price  
FROM products  
WHERE price BETWEEN 100 AND 500;
  • Retrieves products with a price between 100 and 500, including 100 and 500.

2. Works with Dates

BETWEEN is often used with date ranges.

SELECT order_id, order_date  
FROM orders  
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30';
  • Retrieves orders placed between January 1, 2024, and June 30, 2024, inclusive.

3. Works with Text Values

For text, BETWEEN compares values alphabetically.

SELECT name  
FROM customers  
WHERE name BETWEEN 'A' AND 'M';
  • Retrieves customer names starting alphabetically from A to M.

4. NOT BETWEEN

You can use NOT BETWEEN to exclude a range of values.

SELECT product_name, price  
FROM products  
WHERE price NOT BETWEEN 100 AND 500;
  • Retrieves products not within the price range of 100 to 500.

 

Example Queries

1. Filter Products by Price Range

SELECT product_name, price  
FROM products  
WHERE price BETWEEN 50 AND 200;
  • Retrieves products priced between 50 and 200.

2. Filter Orders by Date Range

SELECT order_id, customer_id, order_date  
FROM orders  
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';
  • Retrieves orders placed in March 2024.

3. Exclude Salaries Outside a Range

SELECT name, salary  
FROM employees  
WHERE salary NOT BETWEEN 3000 AND 7000;
  • Retrieves employees with salaries below 3000 or above 7000.

4. Filter Names Alphabetically

SELECT name  
FROM employees  
WHERE name BETWEEN 'D' AND 'K';
  • Retrieves employee names starting with letters between D and K.

 

Combining BETWEEN with Other Conditions

BETWEEN can be combined with AND or OR for more specific filtering.

SELECT name, salary, department  
FROM employees  
WHERE salary BETWEEN 4000 AND 8000 AND department = 'IT';
  • Retrieves employees in the IT department with salaries between 4000 and 8000.

 

Summary

The BETWEEN operator in SQL is a powerful tool for filtering values within a specific range. It is inclusive of both boundaries and can be used with numbers, dates, and text values. For exclusion, the NOT BETWEEN variation provides the opposite effect.

Previous