Clauses and Logical Operators for Retrieving Table Data

In this sixth part of a nine-part series that focuses on retrieving data from tables with the SELECT statement, you’ll learn how to code the WHERE clause and how to use comparison and logical operators. 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 code the WHERE clause

The WHERE clause in a SELECT statement filters the rows in the base table so that only the rows you need are retrieved. In the topics that follow, you’ll learn a variety of ways to code this clause.

How to use the comparison operators

Figure 3-11 shows you how to use the comparison operators in the search condition of a WHERE clause. As you can see in the syntax summary at the top of this figure, you use a comparison operator to compare two expressions. If the result of the comparison is True, the row being tested is included in the query results.

The examples in this figure show how to use some of the comparison operators. The first WHERE clause, for example, uses the equal operator (=) to retrieve only those rows whose vendor_state column have a value of IA. Since the state code is a string literal, it must be enclosed in single quotes.

In contrast, a numeric literal like the one in the second WHERE clause isn’t enclosed in quotes. This clause uses the greater than (>) operator to retrieve only those rows that have a balance due greater than zero.

The third WHERE clause illustrates another way to retrieve all the invoices with a balance due. Like the second clause, it uses the greater than operator. Instead of comparing the balance due to a value of zero, however, it compares the invoice total to the total of the payments and credits that have been applied to the invoice.

The fourth WHERE clause illustrates how you can use comparison operators other than the equal operator with string data. In this example, the less than operator (<) is used to compare the value of the vendor_name column to a literal string that has the letter M in the first position. That will cause the query to return all vendors with names that begin with the letters A through L.

You can also use the comparison operators with date literals, as illustrated by the fifth and sixth WHERE clauses. The fifth clause will retrieve rows with invoice dates on or before May 31, 2008, and the sixth clause will retrieve rows with invoice dates on or after May 1, 2008. Like string literals, date literals must be enclosed in single quotes. In addition, you can use different formats to specify dates, as shown by the two date literals shown in this figure. You’ll learn more about the acceptable date formats and date comparisons in chapter 8.

The last WHERE clause shows how you can test for a not equal condition. To do that, you code a less than sign followed by a greater than sign. In this case, only rows with a credit total that’s not equal to zero will be retrieved.

Whenever possible, you should compare expressions that have similar data types. If you attempt to compare expressions that have different data types, Oracle may implicitly convert the data types for you. Although implicit conversions are often acceptable, they will occasionally yield unexpected results. In chapter 8, you’ll learn how to explicitly convert data types so your comparisons will always yield the results that you want.

The syntax of the WHERE clause with comparison operators

[code]WHERE expression_1 operator expression_2

The comparison operators

Examples of WHERE clauses that retrieve…

Vendors located in Iowa

[code]WHERE vendor_state = 'IA'[/code]

Invoices with a balance due (two variations)

[code]WHERE invoice_total - payment_total - credit_total > 0 WHERE invoice_total > payment_total + credit_total[/code]

Vendors with names from A to L

[code]WHERE vendor_name < 'M'[/code]

Invoices on or before a specified date

[code]WHERE invoice_date <= '31-MAY-08'[/code]

Invoices on or after a specified date

[code]WHERE invoice_date > = '01-MAY-08'[/code]

Invoices with credits that don’t equal zero

[code]WHERE credit_total <> 0[/code]

Description

  1. You can use a comparison operator to compare any two expressions that result in like data types. Although unlike data types may be converted to data types that can be compared, the comparison may produce unexpected results.
  2. If the result of a comparison results in a True value, the row being tested is included in the result set. If it’s False or Unknown, the row isn’t included.
  3. To use a string literal or a date literal in a comparison, enclose it in quotes. To use a numeric literal, enter the number without quotes.
  4. Character comparisons are case-sensitive. ‘CA’ and ‘Ca’, for example, are not equivalent.

How to use the comparison operators

{mospagebreak title=How to use the AND, OR, and NOT logical operators}

You can use the AND and OR operators to combine two or more search conditions into a compound condition. And you can use the NOT operator to negate a search condition.

The first two examples illustrate the difference between the AND and OR operators. When you use the AND operator, both conditions must be true. So in the first example, only those vendors located in Springfield, New Jersey, are retrieved from the Vendors table. When you use the OR operator, though, only one of the conditions must be true. So in the second example, all vendors located in New Jersey and all the vendors located in Pittsburgh are retrieved.

The third example shows a compound condition that uses two NOT operators. As you can see, this expression is difficult to understand. Because of that, you should avoid using this operator. The fourth example in this figure, for instance, shows how the search condition in the third example can be rephrased to eliminate the NOT operator. As a result, the condition in the fourth example is much easier to understand.

The last two examples in this figure show how the order of precedence for the logical operators and the use of parentheses affect the result of a search condition. By default, the NOT operator is evaluated first, followed by AND and then OR. However, you can use parentheses to override the order of precedence or to clarify a logical expression, just as you can with arithmetic expressions. In the next to last example, for instance, no parentheses are used, so the two conditions connected by the AND operator are evaluated first. In the last example, though, parentheses are used so the two conditions connected by the OR operator are evaluated first.

The syntax of the WHERE clause with logical operators

[code]WHERE [NOT] search_condition_1 {AND|OR} [NOT] search_condition_2 ...[/code]

Examples of queries using logical operators

A search condition that uses the AND operator

[code]WHERE vendor_state = 'NJ' AND vendor_city = 'Springfield'[/code]

A search condition that uses the OR operator

[code]WHERE vendor_state = 'NJ' OR vendor_city = 'Pittsburgh'[/code]

A search condition that uses the NOT operator

[code]WHERE NOT (invoice_total >= 5000 OR NOT invoice_date < = '01-JUL-2008')[/code]

The same condition rephrased to eliminate the NOT operator

[code]WHERE invoice_total < 5000 AND invoice_date <= '01-JUL-2008'[/code]

A compound condition without parentheses

[code]SELECT invoice_number, invoice_date, invoice_total FROM invoices WHERE invoice_date > '01-MAY-2008' OR invoice_total > 500 AND invoice_total - payment_total - credit_total > 0 ORDER BY invoice_number [/code]


(91 rows selected)

The same compound condition with parentheses

[code]WHERE (invoice_date > '01-MAY-2008' OR invoice_total > 500) AND invoice_total - payment_total - credit_total > 0 ORDER BY invoice_number [/code]


(39 rows selected)

Description

  1. You can use the AND and OR logical operators to create compound conditions that consist of two or more conditions. You use the AND operator to specify that the search must satisfy both of the conditions, and you use the OR operator to specify that the search must satisfy at least one of the conditions.
  2. You can use the NOT operator to negate a condition, but that can make the search condition difficult to understand. If it does, you should rephrase the condition to eliminate NOT.
  3. When Oracle evaluates a compound condition, it evaluates the operators in this sequence: (1) NOT, (2) AND, and (3) OR. You can use parentheses to override this order of precedence or to clarify the sequence in which the operations will be evaluated.
Please check back for the next part of the series.
[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye