SQL BETWEEN Operator

SQL BETWEEN Operator

The SQL BETWEEN operator is used to filter the result set within a certain range. This command is essential for retrieving rows with column values that fall between two specified values, including the boundary values.

---

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• SELECT column_name(s): Specifies the columns to retrieve.
• FROM table_name: Specifies the table to query data from.
• WHERE column_name BETWEEN value1 AND value2: Filters the result set to include only rows where the column value is between value1 and value2 (inclusive).

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the BETWEEN operator to filter the result set.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named sales within the previously created database.

USE example_db;

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
);

Here, we define the sales table with columns for sale_id, product_name, sale_date, quantity, and price. The sale_id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the sales table.

INSERT INTO sales (product_name, sale_date, quantity, price) VALUES ('Laptop', '2023-01-01', 2, 999.99);
INSERT INTO sales (product_name, sale_date, quantity, price) VALUES ('Smartphone', '2023-01-05', 1, 499.99);
INSERT INTO sales (product_name, sale_date, quantity, price) VALUES ('Tablet', '2023-01-10', 3, 299.99);
INSERT INTO sales (product_name, sale_date, quantity, price) VALUES ('Desk Chair', '2023-01-15', 5, 199.99);
INSERT INTO sales (product_name, sale_date, quantity, price) VALUES ('Bookshelf', '2023-01-20', 2, 89.99);
INSERT INTO sales (product_name, sale_date, quantity, price) VALUES ('Coffee Table', '2023-01-25', 1, 149.99);

Here, we insert six rows of data into the sales table.

Step 4: Using the BETWEEN Operator

This step involves using the BETWEEN operator to find sales that occurred between '2023-01-05' and '2023-01-20'.

SELECT sale_id, product_name, sale_date, quantity, price
FROM sales
WHERE sale_date BETWEEN '2023-01-05' AND '2023-01-20';

This command retrieves the sale_id, product_name, sale_date, quantity, and price of sales that occurred between '2023-01-05' and '2023-01-20' (inclusive).

In this example, the query will return the rows for 'Smartphone', 'Tablet', 'Desk Chair', and 'Bookshelf' as their sale dates fall within the specified range.