PostgreSQL BETWEEN Operator

In PostgreSQL, the BETWEEN operator is used with the WHERE clause of a query to match a column value or an expression against a list of values. For example, get the data from a table where the value in the column falls between x and y.

The BETWEEN operator is used with the numeric and date columns only.

column BETWEEN <low_value> AND <high_value>

If the value is greater than or equal to low_value and less than or equal to high_value then the expression evaluates to be true, otherwise expression evaluates to be false. The BETWEEN operator always includes both the lower range and higher range while evaluating the expression.

The above is same as below, without using the BETWEEN operator.

column &gt;= <low_value> AND column &lt;= <high_value>

Let's use the following Employee table to understand the BETWEEN operator.

Now, let's get all employees whose salary is between 20000 and 50000. For this, we can use the BETWEEN operator with low and high values, as shown below:

SELECT * FROM Employee 
WHERE salary BETWEEN 20000 AND 50000;

Above you can see, emp_id 1 has a salary of 20000, which is lower range value in the BETWEEN operator, is also considered while evaluating BETWEEN operator.

The BETWEEN operator can be used for specifying the date range in the query. The following fetches employees whose hire_date is between 1st Jan 2010 and 31st Dec 2015.

SELECT * FROM Employee 
WHERE hire_date BETWEEN DATE '2010-01-01' and '2015-12-31';

Note that the null value is not considered when filtering data with BETWEEN operator.

The BETWEEN operator can also be written using greater than or equal (>=) and less than or equal (<=) operators, as shown bellow.

SELECT * FROM Employee 
WHERE salary >= 20000 AND salary <= 50000;

NOT BETWEEN

The NOT operator can be used with BETWEEN operator to validate the expression to check values outside the range.

The above is the same as below query using OR operator:

Note: The between clause cannot be used with string columns/values.