PostgreSQL Between Condition

In this section, we are going to understand the working of PostgreSQL Between condition, which is used with the WHERE clause to select data from the table between two defined conditions. We will also see the examples of Between operator with Numeric and Date values and Between operator with a Not Operator.

Introduction of PostgreSQL Between condition

The PostgreSQL Between condition is used to define how to retrieve values from an expression within a specific range. In other words, we can say that the Between condition is used to match a value against a range of values.

The PostgreSQL Between condition is also called as PostgreSQL Between Operator.

PostgreSQL Between Condition Syntax

In PostgreSQL, the Between Condition can be used with the SELECT, INSERT, UPDATE, and DELETE commands.

expression BETWEEN value1 AND value2;    

OR

expression BETWEEN low AND high;

The expression returns true if the value is greater than or equal (>=) to the value1 or low value and less than or equal (<=) to the value2 or High value; else, it returns false.

We can also rewrite the Between operator by using the greater than or equal (>=) or less than or equal (<=) operators, as we can see in the below syntax:

expression >= value1 and value <= value2

Suppose we want to find the value which is out of a range. In that case, we can join the NOT operator with the BETWEEN condition, as we can see in the following syntax:

Expression NOT BETWEEN low AND high;

The below syntax is equivalent to the above syntax, which uses the NOT and BETWEEN operators:

Expression < low OR value > high

In the above syntax, we have used the following parameters:

Examples of PostgreSQL BETWEEN Condition

Let us see different examples to understand how the PostgreSQL Between Condition works:

Example of PostgreSQL Between Condition: with Numeric values

In the below example, we will display how to use the BETWEEN condition with numeric values.

For this, we are taking the Customer table from the TheDeveloperBlog database to get those customers whose age is between 23 and 26.

We are using the Between operator with WHERE clause as we can see in the following command:

SELECT cust_id, cust_name, Cust_age
FROM customer
WHERE cust_age BETWEEN 23 AND 26
ORDER BY cust_age DESC;

Output

After executing the above command, we will get the following output:

In the above command, we can also use the greater than or equal (>=) or less than or equal (<=) operators in place of the BETWEEN operator. This command will give a similar outcome as compared to the above command:

SELECT cust_id, cust_name, Cust_age
FROM customer
WHERE cust_age >=23 
AND cust_age <=26
ORDER BY cust_age DESC;

Output

After successful execution of the above command, we will get the following result:

As we can see in the above images, both the outputs are providing similar results, but using the PostgreSQL Between condition makes the command more comfortable to read and more efficient as compared to the greater than or equal (>=) or less than or equal (<=) Operators.

Example of PostgreSQL Between Condition: with Date

We will see how to use the Date value in the BETWEEN operator to define the literal date in ISO 8601 format: YYYY-MM-DD.

Here, we are taking the department table from the TheDeveloperBlog database.

In the following example, we used the Between Operator to get the information of those employees who joined the particular department between 2017-10-12 to 2020-06-22.

SELECT emp_fname, dept_id, dept_name, joining_date
FROM department
WHERE joining_date BETWEEN '2017-10-12' AND '2020-06-22'
ORDER BY dept_id DESC;

Output

After successfully executing the above command, we will get the following result:

Note: Make sure that the range will always stats with the lower value because if we start giving the range from a higher value, it will give the empty result set.

For example: If we provide the range as the higher value (2020-06-22) and lower value (2017-10-12) in the above command:

SELECT emp_fname, dept_id, dept_name, joining_date
FROM department
WHERE joining_date BETWEEN '2020-06-22' AND '2017-10-12'
ORDER BY dept_id DESC;

Output

On executing the above command, we will get the empty table:

In PostgreSQL, we can also use the greater than or equal (>=) or less than or equal (<=) operators in place of the BETWEEN operator. Then the command will give a similar outcome as compared to the above command:

SELECT emp_fname, dept_id, dept_name, joining_date
FROM department
WHERE joining_date >='2017-10-12'
AND joining_date <='2020-06-22'
ORDER BY dept_id DESC;

Output

On executing the above command, we will get the below outcome:

After implementing both the commands, we can see that the command where we use the Between operator is more readable and shorter as compared to the command where we use the greater than or equal (>=) or less than or equal (<=) operators.

In other words, we can say that PostgreSQL implements the command with the Between operator faster rather than using the other operators.

Example of using NOT Operator with Between Condition

In the PostgreSQL, we can use the NOT operator with Between Operator as well within the WHERE clause to fetch data from a table where the defined condition contradicts the PostgreSQL between condition.

If we want to get those customers whose age is not between the range of 23 and 26, we can use the below command:

SELECT cust_id, cust_name, Cust_age
FROM customer
WHERE cust_age NOT BETWEEN 23 AND 26
ORDER BY cust_age DESC;

Output

After implementing the above command, we will get the following output:

In the below example, we are using the greater than (>) and less than (<) operators with OR operator instead of using NOT BETWEEN/ AND operators. The below command is equivalent to the above SELECT query:

SELECT cust_id, cust_name, Cust_age
FROM customer
WHERE cust_age < 23  
OR cust_age >26
ORDER BY cust_age DESC;

Output

After implementing the above command, we will get the following result:

Overview

In the PostgreSQL Between Condition section, we have learned the following topics:

• The use of PostgreSQL Between operator is used to match a value against a range of values.
• We used the Between Condition to fetch the Numeric values from the particular table.
• We used the Between Condition to fetch the Data values from the particular table.
• We used the NOT Operator with the PostgreSQL Between Operator to get those values that do not satisfy the Between condition.