Introduction to BETWEEN in PostgreSQL
Between in PostgreSQL used to retrieve records from a table with a specified range, it is used in select, delete, update and insert statement. We can use between conditions to retrieve records of numeric and date data type columns, numeric and date data type is most useful to retrieve the record from a table using between the condition in PostgreSQL. Between conditions is most important in PostgreSQL to retrieve a record of a specified range. Between conditions in PostgreSQL used with where clause fetch the data from tables with the help of two specified conditions.
Syntax
Below is the syntax of between conditions in PostgreSQL is as follows.
Select column1, …., columnN from table_name
Where,
Column_name (search condition) BETWEEN (Condition) value1 AND Value2
Expression (Expression used to define query)
BETWEEN (condition) value1 AND Value2
Select *(select all column from selected table) from table_name
Where
Column_name (search condition) BETWEEN (Condition) value1 AND Value2
Select *(select all column from selected table) from table_name
Where
Column_name (search condition) NOT BETWEEN (Condition) value1 AND Value2
Below is the parameter description of the above syntax is as follows.
Parameter:
- Select: Select statement is used to select the column from the table to retrieve data using between conditions.
- Column 1 to column N: Number of columns selected from the table using the select statement to retrieve a record by using between conditions.
- From: Keyword used to define which table name was used to retrieve the data.
- Table name: Name of the table from which we have fetched data by using between conditions.
- Asterisk (*): Asterisk defines, we have selected all columns from the table to retrieve data using between conditions in PostgreSQL.
- Between: Between is a condition in PostgreSQL used to retrieve records from tables with a specified range.
- And: And operator is used with between conditions to select a range to records.
- Value1 and value2: It defines the range of records that are used to fetch the data using between conditions from the table.
- Expression: Expression is nothing but a number of the column selected from a table using the select statement to retrieve a record by using between conditions.
- Not between: Not operator used with between operator to fetch record from table.
How BETWEEN Condition Works in PostgreSQL?
- Below is the working of between conditions in PostgreSQL is as follows.
- PostgreSQL between conditions is used to match the values of table rows against a range value that we have specified in a query.
- We can use the operator in PostgreSQL to match the values with the range of other values.
- We can use a low and high value in a select statement with between conditions to retrieve or fetch data from a table. If the expression first value is less than the expression second value then the expression will return a true value or if the expression first value is greater than expression second value then the expression will return false.
- We can write the between operator or condition by using less than or equal to (<=) or greater than or equal to (>=) operator in SQL statement.
- We can also use not-operator using between conditions to check the value of the expression is out of range in the same situation we combine between operator with not operator in PostgreSQL.
- We can use between the operator in where clause of the following statement in PostgreSQL.
- Select
- Update
- Insert
- Delete
- We can specify the range of two values by using AND operator in between condition. AND operator is used as a mediator of two values that we have used to fetch data from tables.
- In PostgreSQL between operators is used with the date and numeric values. We can match date and numeric values against a range of values in PostgreSQL by using a between operator.
- At the time we have used a numeric value, it will help us to retrieve values from tables that were lie with a specified range.
- At the time we have used a date value, it will help us to retrieve values from tables that were lie with a certain range.
- At the time we have used with not operator, between the operator in PostgreSQL will returns the values which was doesn’t lie within that specified range.
- Between operators in PostgreSQL can be used in a date values. It means that we specify a range of date values.
- We can used or combine not operator with between operators in PostgreSQL. In such a scenario the list of table values which was not in a specified range will be returned.
- We used between the operator in PostgreSQL to select values which was is in range. The range between low to high.
- If values range between low to high in such scenario expression will return true condition.
- If values range between high to low in such scenario expression will return false condition.
Examples to Implement BETWEEN in PostgreSQL
- We have used the employee table to describe the example.
Example #1
Example of employee table.
testing=# select * from Employee;
Example #2 – Between Operator Using Numeric Values
- Below is the example of between operators by using numeric values are as follows.
- In the below example, we have retrieved records from the employee table which have employee salary Between 20000 to 50000.
testing=# SELECT * FROM Employee WHERE emp_salary BETWEEN 20000 AND 50000;
Example #3 – Between Operator Using Date Values
- Below is the example of between operators by using date values are as follows.
- In the below example, we have to retrieve a record from the employee table which has a date of joining of employee Between 2020-02-01 to 2020-02-25.
testing=# SELECT * FROM Employee WHERE date_of_joining BETWEEN '2020-02-01' AND '2020-02-25';
Example #4 – Between Operator Using Not Operator
- Below is the example of between operators by using not operator are as follows.
- In the below example, we have to retrieve a record from the employee table which has employee salary, not Between 20000 to 50000.
testing=# SELECT * FROM Employee WHERE emp_salary NOT BETWEEN 20000 AND 50000;
Conclusion
Between operators is most important in PostgreSQL to retrieve a record from a table with a specified range. We can use between condition to retrieve records of numeric and date type columns, numeric and date data types is mostly useful to retrieve a record from the table using between the condition in PostgreSQL.
Recommended Articles
This is a guide to the BETWEEN in PostgreSQL. Here we discuss the definition and how BETWEEN condition works in PostgreSQL along with different examples and its code implementation. You may also look at the following articles to learn more –
