YogeshChauhan . com

PostgreSQL BETWEEN

in Postgres on November 1, 2020

BETWEEN operator/construct = useful to match a value against a range of values.

The syntax is quite like this:


value_of_rows BETWEEN x AND y;

x and y are just end points and they are inclusive.

You can rewrite the same syntax from above without using BETWEEN as well:


value_of_rows >= x AND value_of_rows <= y;

Let’s take a look at the examples of both:

I am using the database available on my Github public repo

Example with BETWEEN:


SELECT product_name, unit_price 
FROM products 
WHERE unit_price 
BETWEEN 40 AND 50;

//Output
product_name.         unit_price 
"Northwoods Cranberry Sauce"	40
"Schoggi Schokolade"	43.9
"Rössle Sauerkraut"	45.6
...
...

Example without BETWEEN


SELECT product_name, unit_price 
FROM products 
WHERE unit_price >= 40 AND unit_price <= 50;

//Output
product_name.         unit_price 
"Northwoods Cranberry Sauce"    40
"Schoggi Schokolade"    43.9
"Rössle Sauerkraut"    45.6
...
...

To get the values out of that range, all we need to do is add NOT.

Example with NOT BETWEEN:


SELECT product_name, unit_price 
FROM products 
WHERE unit_price 
NOT BETWEEN 40 AND 50;

//Output
product_name.  unit_price 
"Chai"	18
"Chang"	19
"Aniseed Syrup"	10
...
...

You can achieve same results without using NOT BETWEEN as well:

Example without NOT BETWEEN:


SELECT product_name, unit_price 
FROM products 
WHERE unit_price < 40 OR unit_price > 50;

//Output
product_name.  unit_price 
"Chai"	18
"Chang"	19
"Aniseed Syrup"	10
...
...

Pay attention to the fact that, we needed to use OR to do so.

dreamhost

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to uninstall Cocoapods from the Mac OS? #4 How to add Read More Read Less Button using JavaScript? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

May 7 How to disable right click and drag and drop of images using jQuery? May 7 How to render Lists in React? May 7 What’s the difference between variables in CSS and SCSS (Sass)? May 7 How to define variables in SCSS (Sass)? May 7 How to show and hide an element on click in React? May 5 Use inline if to make a shorter conditional syntax in React

You might also like these

Learn how to give a temporary name to a column or to a table using SQL AliasesSQL/MySQLSpecificity of attribute selectorsCSS2 Ways we can create an Array in JavaScriptJavaScriptCommon Table Expressions (CTE) in PostgreSQLPostgresHow to create a link tag button using CSS?CSSHow to enable GD library support for PHP on windows server?PHP