1- Where子句

1．Between…And子句：

When using BETWEEN, make sure the first value is the lowest of the two values provided.    While "BETWEEN 1 AND 10" and "BETWEEN 10 AND 1" might seem logically equivalent, specifying the higher value first guarantees that your condition will always evaluate to FALSE

 

2． NULL关键字：

When working with NULL, the concept of equality does not apply; a column may be NULL, but it will never equal NULL.

 

Oracle doesn't complain if you mistakenly use the equality operator when evaluating for NULL,The following query will parse and execute but will never return rows:

    SELECT order_nbr, cust_nbr, sale_price, order_dt FROM cust_order WHERE ship_dt = NULL;

 

NVL substitutes a specified value for columns that are NULL, as in:

    SELECT fname, lname, manager_emp_id　 FROM employee

     WHERE NVL(manager_emp_id, -999) != 11;

 

In this example, the value -999 is substituted for all NULL values, which, since -999 is never equal to 11, guarantees that all rows whose manager_emp_id column is NULL will be included in the result set. Thus, all employees whose manager_emp_id column is NULL or is not NULL and has a value other than 11 will be retrieved by the query.

 

3.如何充分利用 WHERE子句：

 here are a few tips to help you make the most of your WHERE clauses:

 A． Check your join conditions carefully. Make sure that each data set in the FROM clause  is properly joined. Keep in mind that some joins require multiple conditions. See    Chapter 3 for more information.

 

B． Avoid unnecessary joins. Just because two data sets in your FROM clause contain the    same column does not necessitate a join condition be added to your WHERE clause. In   some designs, redundant data has been propagated to multiple tables through a process     called denormalization.

C． Use parentheses. Oracle maintains both operator precedence and condition precedence, 　　　　　 　 meaning there are clearly defined rules for the order in which things will be evaluated

　     but the safest route for you and for those who will later maintain your code is    to　 dictate　evaluation order using parentheses. For operators, specifying    (5 * p.inventory_qty) + 2 rather than 5 * p.inventory_qty + 2 makes the order in  which the operations should be performed clear. For conditions, use parentheses any   time the OR operator is employed. 

 

D．Handle NULLs properly. After writing your WHERE clause, inspect each condition with  respect to its ability to properly handle NULL values. Take the time to understand  the table definitions in your database so that you know which columns allow NULLs.