Hello! In this tutorial we are going to talk about the SQL BETWEEN command, a very useful operator for searching a range of values. If you are a web developer and want to learn how to use it, keep reading!
- SQL BETWEEN Command Definition and Syntax
- Examples of using the BETWEEN command
- Example 1: SELECT query with BETWEEN
- Example 2: UPDATE query with BETWEEN
- Example 3: INSERT query with BETWEEN
- Related content
SQL BETWEEN Command Definition and Syntax
The BETWEEN command is used in SQL to search for values within a specified range. The basic syntax is as follows:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Where column_name is the name of the column we want to find, table_name is the name of the table where the column is located, value1 and value2 are the range limits.
Examples of using the BETWEEN command
Let’s look at some practical examples of how the BETWEEN command is used in different types of SQL queries.
Example 1: SELECT query with BETWEEN
Suppose we want to get all the products whose price is between 10 and 20 euros in a table called products. The SQL query that we can use is the following:
SELECT * FROM productos WHERE precio BETWEEN 10 AND 20;
The previous query will select all the products whose price is between 10 and 20 euros, both included.
Example 2: UPDATE query with BETWEEN
Now suppose we want to update the stock of products whose price is between 10 and 20 euros in the same products table. The SQL query that we can use is the following:
UPDATE productos SET stock = stock + 10 WHERE precio BETWEEN 10 AND 20;
The previous query will update the stock of all products whose price is between 10 and 20 euros, adding 10 units to the current stock.
Example 3: INSERT query with BETWEEN
Finally, suppose we want to insert a new record into the orders table with the current date, but only if the current date falls between two dates specified in the query. The SQL query that we can use is the following:
INSERT INTO pedidos (id_cliente, fecha) VALUES (123, NOW()) WHERE NOW() BETWEEN '2022-01-01' AND '2022-03-31';
The above query will insert a new record into the orders table with customer_id equal to 123 and the current date, but only if the current date is between January 1, 2022 and March 31, 2022.
Here are some topics related to the BETWEEN command in SQL that may interest you:
- Comparison operators in SQL: In addition to BETWEEN, there are other comparison operators that you can use in your SQL queries, such as =, >, <, >=, and <=.
- Date functions in SQL – If you need to work with dates in SQL, there are several functions that you may find useful, such as NOW(), DATE(), YEAR(), MONTH(), and DAY().
- WHERE clause in SQL – The BETWEEN command is typically used in conjunction with the WHERE clause in SQL to filter records from a table that meet certain conditions. If you want to go deeper into the use of the WHERE clause, we recommend that you consult our tutorial on the subject.
We hope you have found this tutorial helpful in understanding how to use the BETWEEN command in your SQL queries. If you have any questions or suggestions, feel free to leave a comment in the section below.