Computer Science
SQL BETWEEN
SQL BETWEEN is a comparison operator used to retrieve values within a specified range. It is used in SQL queries to filter data based on a range of values for a particular column. The BETWEEN operator is inclusive, meaning it includes the start and end values in the range.
Written by Perlego with AI-assistance
Related key terms
1 of 5
5 Key excerpts on "SQL BETWEEN"
eBook - PDF- Paul Wilton, John Colby(Authors)
- 2005(Publication Date)
- Wrox(Publisher)
Until now, when you needed to check for a value within a certain range, you used the “greater than or equal to” operator (>=) or the “less than or equal to” (<=) operator. 66 Chapter 3 The BETWEEN operator functions exactly the same way, except it’s shorter — it saves on typing and also makes the SQL more readable. The following SQL uses the BETWEEN operator to select films with a rating between 3 and 5: SELECT FilmName, Rating FROM Films WHERE Rating BETWEEN 3 AND 5 If you use the BETWEEN operator, you see that it provides exactly the same results as the “greater than or equal to” (>=) and “less than or equal to” (<=) operators do. It is extremely important to remember that the BETWEEN operator is inclusive, meaning that in the preceding code, 3 and 5 are also included in the range. You can use BETWEEN with data types other than numbers, such as text and dates. You can also use the BETWEEN operator in conjunction with the NOT operator, in which case SQL selects a value that is not in the range specified, as you see in the following Try It Out. Try It Out Using the NOT and BETWEEN Operators 1. The film club has added more films to its database. The SQL to add the films is listed below and must be executed against the database.
eBook - PDF- Lee Chao(Author)
- 2013(Publication Date)
- Auerbach Publications(Publisher)
BETWEEN The data within the range specified by a BETWEEN operator will be selected by the query. EXISTS The data will be selected if a subquery in an EXISTS operator returns any rows. IN If a value matches at least one of the values specified by an IN operation, the data corresponding to that value will be returned. IS NULL The data will be selected if a column in the search condition contains any null (unknown) value. LIKE The data will be selected if a given value matches a specific pattern defined in a LIKE operator. Figure 6.16 Use OR logical operator. Figure 6.17 Use AND logical operator. Querying Information in Windows Azure SQL Database ◾ 207 There is only one row that matches both conditions of the AND operator. The example in Figure 6.18 uses the BETWEEN operator to define the grade range between A and C. The information is about students who have earned the grade C or better. The next example shows the use of the IN operator. Like the OR operator, if a value matches one of the values specified by the IN operator, the data corresponding to that value will be selected. In Figure 6.19, classes enrolled by a list of students specified by the IN operator are selected. Six rows are selected; two rows match the search condition StudentID 11 and the other four rows match the search condition StudentID 10. The LIKE operator is often used with wildcard operators. The common wildcard operators are shown in Table 6.2. Figure 6.20 shows how to use a wildcard operator with an example that selects students whose last names start with a letter between A and D. Figure 6.18 Use BETWEEN logical operator. Figure 6.19 Use IN logical operator. 208 ◾ Cloud Database Development and Management Five students with their last names starting from A to D are selected. In the next example, we are going to use the NOT operator to select students who are not advised by the faculty member with the faculty id 3 (Figure 6.21).
- Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
- 2012(Publication Date)
- Wiley(Publisher)
Using BETWEEN and IN Another way to filter results is to use the BETWEEN and IN keywords. BETWEEN filters values in a range. You might use BETWEEN to retrieve recent orders by order number or to retrieve records from a table by date. Say that you include a date and time stamp when you enter new customer records. You could then, for example, use BETWEEN to find all customer records added between 1990 and 1999. Suppose that you want to find the customer records for those customers whose customer numbers are between 1000 and 1700, inclusive (meaning that both 1000 and 1700, as well as all numbers in between, are included). Rather than using the AND operator, you could use BETWEEN, as in the following: SELECT CUSTNUM, CUSTNAME FROM CUSTOMER WHERE CUSTNUM BETWEEN 1000 AND 1700 The qualifying records are: CUSTNUM CUSTNAME 1047 Acme Hardware Store 1525 Fred’s Tool Stores 1700 XYZ Stores Suppose that you want to find the customer records for those customers headquartered in Atlanta, Chicago, or Washington. Rather than using the OR operator, you can use the IN keyword with a list of qualifying values, as in the following query: SELECT CUSTNUM, HQCITY FROM CUSTOMER WHERE HQCITY IN (’Atlanta’, ’Chicago’, Washington’) This returns: CUSTNUM CUSTNAME 0839 Chicago 1525 Atlanta 1700 Washington 7.1.3 MANAGING YOUR RESULT SET 229 Using LIKE You get even more flexibility when you use the LIKE operator and its supported wildcard characters for pattern matching, so you can filter by results that match any string of characters (%), any single character (__), characters in a range or set ([]), or that don’t match a specific character ([^]). Common uses of the LIKE keyword are to retrieve customers by name or geographically by the first few numbers in the customer’s ZIP code.
eBook - PDF- Jan L. Harrington(Author)
- 2003(Publication Date)
- Morgan Kaufmann(Publisher)
The query would be written SELECT order_numb, customer_numb, order_date FROM orders WHERE order date BETWEEN '2004-10-1' AND '2005-1-1' It produces the output in Figure 4-14. order numb customer numb order date 1 1 12/5/04 3 2 11/12/04 7 3 12/2/04 8 4 11/22/04 14 7 12/13/04 16 8 10/12/04 20 10 11/15/04 25 12 10/10/04 Figure 4-14: Using BETWEEN to retrieve rows in a date range The inverse query to retrieve all orders not placed between 10/1/ 2004 and 1/1/2005 is written SELECT order_numb, customer_numb, order_date FROM orders WHERE order date NOT BETWEEN '2004-10-1' AND '2005-1-1' and produces the output in Figure 4-15. 60 SIMPLESQL RETRIEVAL order numb customer num order date 2 1 2005-07-06 4 2 2005-03-18 5 2 2005-07-06 6 3 2004-08-15 9 4 2005-01 -06 10 5 2005 - 03 - 12 11 6 2004- 09-19 12 6 2005-03-12 13 6 2005-07-21 15 7 2005-01 -09 17 8 2005-02-22 18 8 2005- 05-13 19 9 2004-07-15 Figure 4-15: Using NOT BETWEENto retrieve rows outside a date range If we want output that is easier to read, we might ask the DBMS to sort the result table by date, SELECT order_numb, customer_numb, order_date FROM orders WHERE order date NOT BETWEEN '2004-10-1' AND '2005-1-1' ORDER BY order date m producing the result in Figure 4-16. order numb customer numb --m 19 9 6 3 11 6 22 11 9 4 15 7 23 11 17 8 21 10 10 5 12 6 4 2 18 8 order date 7/15/99 8/15/99 9/19/99 9/19/99 1/6/00 1/9/00 2/21/00 2/22/00 3/4/00 3/12/00 3/12/00 3/18/00 5/13/00 Figure 4-16: Sorted output by date CHOOSING ROWS 61 The Relational Algebra Restrict Operation Row selection criteria ask a DBMS to perform a relational algebra restrict operation, which creates a horizontal subset of a table by comparing rows to logical criteria. The most important thing to keep in mind about restrict is that it cannot be used to specify col- umns. Therefore, a query that asks for specific columns and specific rows requires a restrict and a project. Note: The restrict operation is more commonly known as select.
eBook - PDF- Joy Starks, Philip Pratt, Mary Last, , Joy Starks, Philip Pratt, Mary Last(Authors)
- 2018(Publication Date)
- Cengage Learning EMEA(Publisher)
Tasks not in the SOM category FIGURE 3-20 Query results 90 Chapter 3 Y O U R T U R N 3-10 List the number, name, and balance of all clients with balances greater than or equal to $1,000 and less than or equal to $5,000. You could use a WHERE clause and the AND operator (Balance>=1000 AND Balance<=5000). An alternative to this approach uses the BETWEEN operator, as shown in Figure 3-21. The BETWEEN operator is inclusive; it includes the lower number, the higher number, and all numbers in-between. BETWEEN operator indicates the value must be between the listed numbers and inclusive FIGURE 3-21 SQL query with the BETWEEN operator 91 The Relational Model 2: SQL The query results appear in Figure 3-22. Clients with balances between $1,000 and $5,000 FIGURE 3-22 Query results The BETWEEN operator is not an essential feature of SQL; you can use the AND operator to obtain the same results. Using the BETWEEN operator, however, does make some SELECT clauses easier to construct. C O M P U T E D F I E L D S Similar to QBE, you can include fields in queries that are not in the database but whose values can be computed from existing database fields. A field whose values you derive from existing fields is called a computed field or calculated field. Computed fields can involve addition (+), subtraction ( – ), multiplication (*), or division (/). The query in Your Turn 3-11, for example, uses subtraction. Y O U R T U R N 3-11 List the number, name, and available credit for all clients. There is no field in the database that stores available credit, but you can compute it using two fields that are present in the database: CreditLimit and Balance. The query design shown in Figure 3-23 creates a new field named AvailableCredit, which is computed by subtracting the value in the Balance field from the value in the CreditLimit field (CreditLimit – Balance). By using the word AS after the computation, followed by AvailableCredit, you can assign a name to the computed field. 92 Chapter 3
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
