Science, Tech, Math › Computer Science Selecting Data Within Ranges in SQL Introducing the WHERE clause and BETWEEN condition Print KIVILCIM PINAR / Getty Images Science, Tech, Math PHP Programming Perl Python Java Programming Javascript Programming Delphi Programming C & C++ Programming Ruby Programming Visual Basic View More By Mike Chapple Mike Chapple Writer University of Idaho Auburn University Notre Dame Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. Learn about our Editorial Process Updated on July 08, 2019 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, UnitPriceFROM productsWHERE UnitPrice >20.00 This produces a list of four products, as shown below: Retrieving Data with SQL Queries: Introducing the SELECT Statement By Mike Chapple Read More ProductName UnitPrice------- --------Chef Anton's Gumbo Mix 21.35Chef Anton's Cajun Seasoning 22.00Grandma's Boysenberry Spread 25.00Uncle 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 ProductNameFROM productsWHERE 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, UnitPriceFROM productsWHERE UnitPrice > 15.00 AND UnitPrice < 20.00 This produces the result shown below: ProductName UnitPrice------- --------Chai 18.00Chang 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, UnitPriceFROM productsWHERE 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 ProductNameFROM productsWHERE ProductName BETWEEN "A" and "D" Which produces the result: ProductName-------Aniseed SyrupChai ChangChef Anton's Gumbo MixChef 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. Cite this Article Format mla apa chicago Your Citation Chapple, Mike. "Selecting Data Within Ranges in SQL." ThoughtCo, Nov. 18, 2021, thoughtco.com/selecting-data-within-ranges-in-sql-1019767. Chapple, Mike. (2021, November 18). Selecting Data Within Ranges in SQL. Retrieved from https://www.thoughtco.com/selecting-data-within-ranges-in-sql-1019767 Chapple, Mike. "Selecting Data Within Ranges in SQL." ThoughtCo. https://www.thoughtco.com/selecting-data-within-ranges-in-sql-1019767 (accessed March 28, 2024). copy citation By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies