In this tutorial, we will be learning to use the BETWEEN operator in PostgreSQL. It is used to check if the given value lies within the specified range. This is going to be a super easy tutorial, so let’s get straight into it.
Introduction to PostgreSQL BETWEEN Operator
When you want to check if a particular value is present between two values, you use the BETWEEN operator. It is very similar to checking the value with the greater than (>) and less than (<) operators.
For example, if you want to check if the person’s age is between 18 to 60, you can use the BETWEEN operator. Another way to check this condition is using the less than and greater than operators.
The BETWEEN operator is used with the WHERE clause in SELECT, UPDATE and DELETE statements to find the targeted rows and perform the next operation.
Note that, if the value lies within the specified range, it returns true, otherwise false.
Now that you have got an idea about what the BETWEEN operator is, let’s see its syntax.
Syntax of PostgreSQL BETWEEN Operator
The following syntax illustrates the syntax of the BETWEEN operator.
Syntax:
value BETWEEN low AND high;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, if the value is greater than or equal to low and less than or equal to high, the query returns true.
Note that, the low value must always be less than the high value. If the value lies within the low and high values but you interchange the low and high values, the query will return false.
The above query is equivalent to:
value >= low AND value<=high;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)If you have multiple conditions in the query, you must enclose the above condition in parentheses.
The BETWEEN operator can be combined with the NOT operator to return the exactly opposite result as of the BETWEEN:
value NOT BETWEEN low AND high;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the query returns true if the value is outside the range of low and high, otherwise false.
The above query is equivalent to:
value < low OR value > high; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)This query will make sure that the value is not between the low and high.
Now let’s see some examples to understand the real-life use of the BETWEEN operator.
Examples of PostgreSQL BETWEEN Operator
To demonstrate the examples, we will need a table first. We have already created a table called ’employees’ as shown below.
Here, we have created a table to store the employees’ information such as name, department, salary etc.
Also Read: How to Create a Table in PostgreSQL?
Now let’s move forward to see the BETWEEN operator examples.
Example 1: Using BETWEEN Operator
We will find all those employees having salaries between 50000 and 55000.
SELECT * FROM employees
WHERE salary BETWEEN 50000 and 55000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, all those employees having the salary between the given range and including those values will be returned. Let’s see the output.
Output:
As you can see, we have got the expected output.
Example 2: Using NOT BETWEEN Operator
Now let’s say you have to find all those employees who have salaries less than 50,000 and greater than 55,000. In this case, we can use the NOT BETWEEN operator.
SELECT * FROM employees
WHERE salary NOT BETWEEN
50000 and 55000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
Example 3: Using BETWEEN with Dates
The BETWEEN operator can be used with numeric values as well as date values. Here we will use the BETWEEN operator to check if the employee has been onboarded in the given time period.
SELECT * FROM employees
WHERE onboarding_date BETWEEN
'1-9-2023' AND '5-9-2023';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the date values must be enclosed within the single quotes. Also, the date values must be in the valid formats as shown here. Let’s see its output.
Output:
As you can see, we have a list of employees who have been onboarded in the given time frame.
Conclusion
In this tutorial, we have learned the syntax, use cases and examples of the BETWEEN operator in PostgreSQL. It is one of the most used operators in any database system for precisely retrieving the data within the specified range. Happy Coding!
Reference
PostgreSQL documentation on functions and operators