Selecting Data Within Ranges in SQL

Introducing the WHERE clause and BETWEEN condition

SQL code
KIVILCIM PINAR / Getty Images

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: 

ProductID ProductName SupplierID QuantityPerUnit UnitPrice UnitsInStock
1 Chai 1 10 boxes x 20 bags 18.00 39
2 Chang 1 24 - 12 oz bottles 19.00 17
3 Aniseed Syrup 1 12 - 550 ml bottles 10.00 13
4 Chef Anton's Cajun Seasoning 2 48 - 6 oz jars 22.00 53
5 Chef Anton's Gumbo Mix 2 36 boxes 21.35 0
6 Grandma's Boysenberry Spread 3 12 - 8 oz jars 25.00 120
7 Uncle Bob's Organic Dried Pears 3 12 - 1 lb pkgs. 30.00 15
Product Table

 

 

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.