Using the IN and BETWEEN Operators on Tables

In this seventh part of a nine-part series, you will learn how to use the IN and BETWEEN operators, along with the SELECT statement, to retrieve data from a single table. This article is excerpted from chapter three of the book Murach’s Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

How to use the IN operator

Figure 3-13 shows how to code a WHERE clause that uses the IN operator. When you use this operator, the value of the test expression is compared with the list of expressions in the IN phrase. If the test expression is equal to one of the expressions in the list, the row is included in the query results. This is illustrated by the first example in this figure, which will return all rows whose terms_id column is equal to 1, 3, or 4.

You can also use the NOT operator with the IN phrase to test for a value that’s not in a list of expressions. This is illustrated by the second example in this figure. In this case, only those vendors who are not in California, Nevada, or Oregon are retrieved.

If you look at the syntax of the IN phrase shown at the top of this figure, you’ll see that you can code a subquery in place of a list of expressions. Subqueries are a powerful tool that you’ll learn about in chapter 6. For now, though, you should know that a subquery is simply a SELECT statement within another statement. In the third example in this figure, for instance, a subquery is used to return a list of vendor_id values for vendors who have invoices dated May 1, 2008. Then, the WHERE clause retrieves a vendor row only if the vendor is in that list. Note that for this to work, the subquery must return a single column, in this case, vendor_id.

The syntax of the WHERE clause with the IN operator

WHERE test_expression [NOT] IN ({subquery|expression_1 [, expression_2]…})

Examples of the IN operator

The IN operator with a list of numeric literals

WHERE terms_id IN (1, 3, 4)

The IN operator preceded by NOT

WHERE vendor_state NOT IN (‘CA’, ‘NV’, ‘OR’)

The IN operator with a subquery

WHERE vendor_id IN
(SELECT vendor_id
FROM invoices
WHERE invoice_date = ’01-MAY-2008′)

Description

  1. You can use the IN operator to test whether an expression is equal to a value in a list of expressions. Each of the expressions in the list must evaluate to the same type of data as the test expression.
  2. The list of expressions can be coded in any order without affecting the order of the rows in the result set.
  3. You can use the NOT operator to test for an expression that’s not in the list of expressions.
  4. You can also compare the test expression to the items in a list returned by a subquery as illustrated by the third example above. You’ll learn more about coding subqueries in chapter 6.

——————————————–Figure 3-13 How to use the IN operator

{mospagebreak title=How to use the BETWEEN operator}

Figure 3-14 shows how to use the BETWEEN operator in a WHERE clause. When you use this operator, the value of a test expression is compared to the range of values specified in the BETWEEN phrase. If the value falls within this range, the row is included in the query results.

The first example in this figure shows a simple WHERE clause that uses the BETWEEN operator. It retrieves invoices with invoice dates between May 1, 2008 and May 31, 2008. Note that the range is inclusive, so invoices with invoice dates of May 1 and May 31 are included in the results.

The second example shows how to use the NOT operator to select rows that are not within a given range. In this case, vendors with zip codes that aren’t between 93600 and 93799 are included in the results.

The third example shows how you can use a calculated value in the test expression. Here, the payment_total and credit_total columns are subtracted from the invoice_total column to give the balance due. Then, this value is compared to the range specified in the BETWEEN phrase.

The last example shows how you can use calculated values in the BETWEEN phrase. Here, the first value selects the function SYSDATE (which represents the current date), and the second value is SYSDATE plus 30 days. So the query results will include all those invoices that are due between the current date and 30 days from the current date.

However, please note the warning about date comparisons in this figure. In particular, an invoice-date of May 31, 2008 at 2:00 PM isn’t less than or equal to “31-May-2008”, and it isn’t between “01-May-2008” and “31-May-2008”. To learn more about date comparisons, please read chapter 8.

The syntax of the WHERE clause with the BETWEEN operator

WHERE test_expression [NOT] BETWEEN begin_expression AND end_expression

Examples of the BETWEEN operator

The BETWEEN operator with literal values

WHERE invoice_date BETWEEN ’01-MAY-2008′ AND ’31-MAY-2008′

The BETWEEN operator preceded by NOT

WHERE vendor_zip_code NOT BETWEEN 93600 AND 93799

The BETWEEN operator with a test expression coded as a calculated value

WHERE invoice_total – payment_total – credit_total BETWEEN 200 AND 500

The BETWEEN operator with the upper and lower limits coded as calculated values

WHERE invoice_due_date BETWEEN SYSDATE AND (SYSDATE + 30)

Description

  1. You can use the BETWEEN operator to test whether an expression falls within a range of values. The lower limit must be coded as the first expression, and the upper limit must be coded as the second expression. Otherwise, the result set will be empty.
  2. The two expressions used in the BETWEEN operator for the range of values are inclusive. That is, the result set will include values that are equal to the upper or lower limit.
  3. You can use the NOT operator to test for an expression that’s not within the given range.

Warning about date comparisons

  1. All columns that have the DATE data type include both a date and time, and so does the value returned by the SYSDATE function. But when you code a date literal like “31-May-2008”, the time defaults to 00:00:00 on a 24-hour clock, or 12:00 AM (midnight). As a result, a date comparison may not yield the results you expect. For instance, May 31, 2008 at 2:00 PM isn’t between “01-May-2008” and “31-May-2008”.
  2. To learn more about date comparisons, please see chapter 8.

——————————————–Figure 3-14 How to use the BETWEEN operator

Please check back regularly for the next part of this series.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan