CAT DURATION --- -------BLD 4 BLD 1 BLD 2 DSG 2 in Java

Creating DataMatrix in Java CAT DURATION --- -------BLD 4 BLD 1 BLD 2 DSG 2

In this example, you can see that the OR operator in SQL is inclusive; otherwise, the third row wouldn t show up in the result. The XML course belongs to the BLD course category (so the first condition evaluates to TRUE) and its duration is two days (so the second condition also evaluates to TRUE). Another point of note regarding the evaluation order for an OR operator is that conditions are evaluated in order until a TRUE condition is found. All subsequent conditions are ignored. This is due to the fact that for an OR operator to be satisfied, only one condition must evaluate to TRUE. So, even if you had many OR conditions, evaluation will stop as soon as the first TRUE occurs. In the upcoming discussion of the NOT operator, you will see how to construct an exclusive OR.

There is a possible problem if your compound conditions contain a mixture of AND and OR operators. See Listing 4-18 for an experiment with a query against the DUAL table. Listing 4-18. Combining Conditions with OR and AND select 'is true ' as condition from dual where 1=1 or 1=0 and 0=1; CONDITION --------is true The compound condition in Listing 4-18 consists of three rather trivial, simple conditions, evaluating to TRUE, FALSE, and FALSE, respectively. But what is the outcome of the compound predicate as a whole, and why Apparently, the compound predicate evaluates to TRUE; otherwise, Listing 4-18 would have returned the message no rows selected. In such cases, the result depends on the operator precedence rules. You can interpret the condition of Listing 4-18 in two ways, as follows:

If one of the operands of OR is true, the overall result is TRUE. If one of the operands of AND is false, the overall result is FALSE.

Listing 4-18 obviously shows an overall result of TRUE. The Oracle DBMS evaluates the expressions in the order that will require the fewest conditional checks. This decision is based on the demographics of your data and is an advanced topic not covered in this book. With compound conditions, it is always better to use parentheses to indicate the order in which you want the operations to be performed, rather than relying on implicit language precedence rules. Listing 4-19 shows two variants of the query from Listing 4-18, using parentheses in the WHERE clause. Listing 4-19. Using Parentheses to Force Operator Precedence select 'is true ' as condition from dual where (1=1 or 1=0) and 0=1; no rows selected select 'is true ' as condition from dual where 1=1 or (1=0 and 0=1); CONDITION --------is true

Caution Remember that you can use white space to beautify your SQL commands; however, never allow an attractive SQL command layout (for example, with suggestive indentations) to confuse you. Tabs, spaces, and new lines may increase statement readability, but they don t change the meaning of your SQL statements in any way.

You can apply the NOT operator to any arbitrary condition to negate that condition. Listing 4-20 shows an example.

Listing 4-20. Using the NOT Operator to Negate Conditions select ename, job, deptno from employees where NOT deptno > 10; ENAME -------CLARK KING MILLER JOB DEPTNO -------- -------MANAGER 10 DIRECTOR 10 ADMIN 10

In this simple case, you could achieve the same effect by removing the NOT operator and changing the comparison operator > into <=, as shown in Listing 4-21. Listing 4-21. Equivalent Query Without Using the NOT Operator select ename, job, deptno from employees where deptno <= 10; ENAME -------CLARK KING MILLER JOB DEPTNO -------- -------MANAGER 10 DIRECTOR 10 ADMIN 10

The NOT operator becomes more interesting and useful in cases where you have complex compound predicates with AND, OR, and parentheses. In such cases, the NOT operator gives you more control over the correctness of your commands. In general, the NOT operator should be placed in front of the condition. Listing 4-22 shows an example of illegal syntax and a typical error message when NOT is positioned incorrectly. Listing 4-22. Using the NOT Operator in the Wrong Place ename, job, deptno employees deptno NOT > 10; deptno NOT > 10 * ERROR at line 3: ORA-00920: invalid relational operator There are some exceptions to this rule. As you will see in Section 4.6, the SQL operators BETWEEN, IN, and LIKE have their own built-in negation option. select from where where