Sql Between Operator with Dates and Numbers

The SQL Between is an operator that enables you to select values within a range in your SQL queries. Additionally, The SQL between operator can use with date ranges.

Undoubtedly the SQL between operator is helpful in many cases. You might need to retrieve from a MySQL database all employees between 20 and 30 years old. Or you might need to retrieve all products sold within a given month. In both cases, the SQL Between operator will help you filter the desired entries from your databases.

In this tutorial, you will learn how to use the SQL Between operator, and we will see examples of how to use this operator with numbers and two dates.

Additionally, in case you want to test out the code snippet in this blog, I have added a section at the end where you will learn how to install MySQL using docker containers. Including how to connect to the container and create your database.

Let’s dive in!

How Does Sql Between work?

As mentioned before, the SQL between operator enables you to retrieve values within a specific range. For example, you could use this operator in a SELECT query using the following syntax:

SELECT * 
FROM table_name
WHERE column_name BETWEEN value1 and value2;

Alternatively, you can also use the SQL between operator in SQL UPDATE statements or in any WHERE clause in a SQL statement. See the syntax below:

UPDATE table_name
SET colum_name1=value
WHERE column_name2 BETWEEN value2 AND value3;

Please note that this operator is inclusive. Also, it’s important to consider that value2 and value3 should be of the same type.

Let’s illustrate all the above with an example. For example, let’s say we have a table named employee which has the following columns:

CREATE TABLE employee (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name varchar(20),
	dept varchar(10),
	salary int(10)
);

And the employee table contains the following data:

+----+-------+---------+--------+
| id | name  | dept    | salary |
+----+-------+---------+--------+
|  1 | Marta | IT      |  70000 |
|  2 | Peter | Sales   |  50000 |
|  3 | John  | Support |  30000 |
+----+-------+---------+--------+

SQL Between Numbers

Now that we have some data let’s see how to use the between operator to filter entries using numbers. For example, let’s say you want to retrieve only the employees that earn a salary ranging from $10000 to $40000. In SQL, you can achieve this as shown below:

SELECT * 
FROM employee 
WHERE salary BETWEEN 10000 AND 40000;

Output:

+----+------+---------+--------+
| id | name | dept    | salary |
+----+------+---------+--------+
|  3 | John | Support |  30000 |
+----+------+---------+--------+

SQL Between two dates?

On many occasions, you might need to filter entries in your database by dates. You can achieve this in SQL using the between operator. To continue with our example, first, I will add a new column called startDate.

ALTER TABLE  employee 
ADD startDate DATETIME NOT NULL DEFAULT now() AFTER salary;

After creating the new datetime column, the next step is updating the dates for some entries to simulate that each employee has started in a different month:

UPDATE employee 
SET startDate='2021-01-20 19:43:30' 
WHERE id=1;

UPDATE employee 
SET startDate='2021-02-20 19:43:30' 
WHERE id=2;

After updating the employee table, this is how our data will look:

+----+-------+---------+--------+---------------------+
| id | name  | dept    | salary | startDate           |
+----+-------+---------+--------+---------------------+
|  1 | Marta | IT      |  70000 | 2021-01-20 19:43:30 |
|  2 | Peter | Sales   |  50000 | 2021-02-20 19:43:30 |
|  3 | John  | Support |  30000 | 2021-03-29 19:43:30 |
+----+-------+---------+--------+---------------------+

Perfect! Now that we have our data ready, let’s say that we would like to retrieve only those employee that started in January. See below how you can achieve this:

SELECT * 
FROM employee 
WHERE startDate BETWEEN '2021-01-01' AND '2021-02-01'

Output:

+----+-------+------+--------+---------------------+
| id | name  | dept | salary | startDate           |
+----+-------+------+--------+---------------------+
|  1 | Marta | IT   |  70000 | 2021-01-20 19:43:30 |
+----+-------+------+--------+---------------------+

Install mysql using Docker containers

In case you would like to test the SQL between operator in a MySQL database, I have added instructions below to guide you about how you can install MySQL in your machine easily using docker containers.

You can think of a docker container as a mini server that is running on your machine. This mini server will have an operating system and specific software determined by the container image you choose to use.

In case you don’t have Docker installed, you can download it from here: Install Docker.

Step 1 – Pull the mysql image

First you will need to pull a Docker image that contains MySQL. In this case, I will be using Mariadb:

 docker pull mariadb:latest

Step 2 – Run the container

Once you have downloaded the image, the next step is running the container. Running the docker container is equivalent to starting a server. As a result, as soon as the container is running, MySQL is up and accessible.

docker run -p 127.0.0.1:3306:3306  --name some-mariadb -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mariadb:latest

The above command will start a MySQL container listening in port 3306, which you can access using the root user and password my-secret-pw.

Step 3 – Access the container

Once your mini server is running, the next step is connecting to this server. To do so, first, you need to find out the container id. Run the following command to do so:

docker ps

Output:

CONTAINER ID   IMAGE            COMMAND                  CREATED        STATUS        PORTS                      NAMES
c5b8dda7bbcd   mariadb:latest   "docker-entrypoint.s…"   24 hours ago   Up 24 hours   127.0.0.1:3306->3306/tcp   some-mariadb

Now that you know your container id, in this case, c5b8dda7bbcd, you can connect to it using the following command:

docker exec -it c5b8dda7bbcd bash 

Step 4 – Access mysql

So far, we have connected to the container where MySQL is installed. So next step is accessing MySQL so we can execute our SQL statements. To access, you need to run the command below:

mysql -uroot -p

Conclusion

In conclusion, you have successfully installed and ran a MySQL container using docker. Additionally, you learn how to use the SQL between operator to retrieve database entries by a specific range. You can use this operator with numbers and with dates as well.

I hope you enjoy this article, and thank you so much for reading and supporting this blog!

Happy Coding!

More Interesting Articles

How To write your Rock Paper Scissors Game in Java

Random Class in Java Simply Explained

How to Write a Tic Tac Toe Game in Java using classes