In this example, we will see how to use the BETWEEN operator included in the SQL.
1. Introduction
The BETWEEN is a logical operator, often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements, and gives us the ability to specify whether a value is in a given range or not.
Syntax
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
2. SQL BETWEEN Example
In this example, we will see the basic usage of BETWEEN operator. We will create a TABLE and we will insert some ROWS. Then we will execute some SELECT queries and see the results.
BasicExample.sql
create table employee (id INT, name VARCHAR(50), date_of_birth DATE); insert into employee (id, name, date_of_birth) values (1, 'John', '2020-01-19 04:41:02'); insert into employee (id, name, date_of_birth) values (2, 'Mary', '2020-03-05 01:23:00'); insert into employee (id, name, date_of_birth) values (3, 'George', '2001-12-26 05:01:00'); insert into employee (id, name, date_of_birth) values (4, 'Dany', '1996-08-04 00:00:00'); insert into employee (id, name, date_of_birth) values (5, 'Bob', '1956-01-30 00:00:00'); SELECT * FROM employee WHERE id BETWEEN 2 AND 4;
As we can see, this query returned the rows with id between 2 and 4 ( 2 and 4 also included).
3. Using > and < instead of BETWEEN
Instead of BETWEEN we can use > and <. The only difference is that we won’t get as result the rows that include the values we compare.
BasicExample.sql
SELECT * FROM employee WHERE id>2 AND id<4;
4. BETWEEN Text Values Example
We can also use the SQL BETWEEN statement by comparing text values. The following SQL statement selects all employees with a name between Dany and John.
BasicExample.sql
SELECT * FROM employee WHERE name BETWEEN 'Dany' AND 'John' ORDER BY id;
5. MYSQL BETWEEN Example with Dates
When we use the BETWEEN operator with DATE values, we have to CAST the type of column or expression to the DATE type.
BasicExample.sql
SELECT * FROM employee WHERE date_of_birth BETWEEN CAST('1992-00-00' AS DATE) AND CAST('2003-01-01' AS DATE);
6. NOT BETWEEN
Also, we can combine BETWEEN and NOT operators to get all the rows that are not between the given range.
BasicExample.sql
SELECT * FROM employee WHERE id NOT BETWEEN 2 AND 4;
SELECT * FROM employee WHERE date_of_birth NOT BETWEEN CAST('1992-00-00' AS DATE) AND CAST('2003-01-01' AS DATE);
7. BETWEEN with IN Example
Also, it is possible to combine the BETWEEN statement with another clause, like the IN statement. In this example, we can see that the row with id 3 is not in the results.
BasicExample.sql
SELECT * FROM employee WHERE id BETWEEN 2 AND 4 AND name NOT IN ('George');
7. Summary
In these examples, we learned how we can use the BETWEEN operator to our advantage on simple data types or DATE type data. Also, the way we can combine different statements in order to get the desired result.
8. Download the source code
This was an SQL Between, MySQL Between Dates, Not Between Example.
You can download the full source code of this example here: SQL Between, MySQL Between Dates, Not Between
Last updated on Oct. 12th, 2021
