How to Use the BETWEEN Operator in MySQL

08/04/2021

Contents

In this article, you will learn how to use the BETWEEN operator in MySQL.

Using the BETWEEN operator in MySQL

The BETWEEN operator in MySQL is used to select values within a specified range. This operator can be used in both the WHERE and JOIN clauses of SQL queries.

Syntax

The basic syntax of the BETWEEN operator in MySQL is as follows:

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

In this syntax, column_name is the name of the column you want to select data from, table_name is the name of the table, and value1 and value2 are the range of values you want to select.

Examples

Suppose we have a table called “employees” with columns “id”, “name”, and “salary”. We want to select all employees whose salary is between $50,000 and $100,000.

SELECT name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 100000;

This query will return the names and salaries of all employees whose salary is between $50,000 and $100,000.

And suppose we have a table called “orders” with columns “id”, “customer_name”, “order_date”, and “order_total”. We want to select all orders placed between January 1, 2022, and March 31, 2022.

SELECT customer_name, order_total
FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-03-31';

This query will return the names and order totals of all orders placed between January 1, 2022, and March 31, 2022.

Using NOT BETWEEN

You can also use the NOT BETWEEN operator to select values that are not within a specified range. Here is an example:

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 100000;

This query will return the names and salaries of all employees whose salary is not between $50,000 and $100,000.

Using BETWEEN with JOIN

You can also use the BETWEEN operator with JOIN to select data from multiple tables. Here is an example:

SELECT customers.customer_name, orders.order_total
FROM customers
JOIN orders
ON customers.id = orders.customer_id
WHERE orders.order_date BETWEEN '2022-01-01' AND '2022-03-31';

This query will return the names and order totals of all orders placed between January 1, 2022, and March 31, 2022, along with the corresponding customer names. The JOIN clause is used to join the “customers” and “orders” tables on the “id” and “customer_id” columns, respectively.

#MySQL