Selecting Data Within Ranges in SQL

Introducing the WHERE clause and BETWEEN condition

The Structured Query Language (SQL) provides database users with the ability to create customized queries to extract information from databases. In an earlier article, we explored extracting information from a database using SQL SELECT queries. Let's expand upon that discussion and explore how you can perform advanced queries to retrieve data that matches specific conditions.

Let's consider an example based on the commonly used Northwind database, which frequently ships with database products as a tutorial.

Here's an excerpt from the database's Product table: 

Product Table
ProductIDProductNameSupplierIDQuantityPerUnitUnitPriceUnitsInStock
1Chai110 boxes x 20 bags18.0039
2Chang124 - 12 oz bottles19.0017
3Aniseed Syrup112 - 550 ml bottles10.0013
4Chef Anton's Cajun Seasoning248 - 6 oz jars22.0053
5Chef Anton's Gumbo Mix236 boxes21.350
6Grandma's Boysenberry Spread312 - 8 oz jars25.00120
7Uncle Bob's Organic Dried Pears312 - 1 lb pkgs.30.0015

 

 

 

 

 

 

 

 

 

 

Simple Boundary Conditions

The first restrictions we will place on our query involve simple boundary conditions. We can specify these in the WHERE clause of the SELECT query, using simple condition statements constructed with standard operators, such as <, >, >=, and <=.


First, let's try a simple query that allows us to extract a list of all the products in the database that have a UnitPrice of more than 20.00:

SELECT ProductName, UnitPrice
FROM products
WHERE UnitPrice >20.00

This produces a list of four products, as shown below:

ProductName                        UnitPrice
-------                            --------
Chef Anton's Gumbo Mix             21.35
Chef Anton's Cajun Seasoning       22.00
Grandma's Boysenberry Spread       25.00
Uncle Bob's Organic Dried Pears    30.00

We can also use the WHERE clause with string values. This basically equates characters to numbers, with A representing the value 1 and Z representing the value 26. For example, we could show all products with names beginning with U, V, W, X, Y or Z with the following query:

SELECT ProductName
FROM products
WHERE ProductName >= 'T'

Which produces the result:

ProductName
-------
Uncle Bob's Organic Dried Pears

Expressing Ranges using Boundaries

The WHERE clause also allows us to implement a range condition on a value by using multiple conditions. For example, if we wanted to take our query above and limit the results to products with prices between 15.00 and 20.00, we could use the following query:

SELECT ProductName, UnitPrice
FROM products
WHERE UnitPrice > 15.00 AND UnitPrice < 20.00

This produces the result shown below:

ProductName   UnitPrice
-------       --------
Chai          18.00
Chang         19.00

Expressing Ranges with BETWEEN

SQL also provides a shortcut BETWEEN syntax that reduces the number of conditions that we need to include and makes the query more readable. For example, instead of using the two WHERE conditions above, we could express the same query as:

SELECT ProductName, UnitPrice
FROM products
WHERE UnitPrice BETWEEN 15.00 AND 20.00

As with our other condition clauses, BETWEEN works with string values as well. If we wanted to produce a list of all countries beginning with V, W or X, we could use the query:

SELECT ProductName
FROM products
WHERE ProductName BETWEEN "A" and "D"

Which produces the result:

ProductName
-------
Aniseed Syrup
Chai 
Chang
Chef Anton's Gumbo Mix
Chef Anton's Cajun Seasoning

The WHERE clause is a powerful part of the SQL language that allows you to restrict results to values falling within specified ranges. It is very commonly used to help express business logic and should be a part of every database professional's toolkit. It's often helpful to incorporate common clauses into a stored procedure to make it accessible to those without SQL knowledge.