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.
OR
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:
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:
The below syntax is equivalent to the above syntax, which uses the NOT and BETWEEN operators:
In the above syntax, we have used the following parameters:
| Parameter | Description |
|---|---|
| Expression | It is used to define a column or field. |
| value1 and value2 | These values define an inclusive range that expression is compared to. |
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 tutoraspire 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:
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:
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 tutoraspire 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.
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:
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:
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:
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:
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.