Team LiB   Previous Section   Next Section

2.4 Conditions and Expressions

Now that we understand how conditions are grouped together and evaluated, let's look at the different elements that make up a condition. A condition is comprised of one or more expressions along with one or more operators. Examples of expressions include:

  • Numbers

  • Columns, such as s.supplier_id

  • Literals, such as 'Acme Industries'

  • Functions, such as UPPER('abcd')

  • Lists of simple expressions, such as (1, 2, 3)

  • Subqueries

Examples of operators include:

  • Arithmetic operators, such as +, -, *, and /

  • Comparison operators, such as =, <, >=, !=, LIKE, and IN

The following sections explore many of the common condition types that use different combinations of the above expression and operator types.

2.4.1 Equality/Inequality Conditions

Most of the conditions that we use when constructing a WHERE clause will be equality conditions used to join data sets together or to isolate specific values. We have already encountered these types of conditions numerous times in previous examples, including: 

s.supplier_id = p.supplier_id

s.name = 'Acme Industries'

supplier_id = (SELECT supplier_id 
  FROM supplier 
  WHERE name = 'Acme Industries')

In all three cases, we have a column expression followed by a comparison operator (=) followed by another expression. The conditions differ in the type of expression on the right side of the comparison operator. The first example compares one column to another, the second example compares a column to a literal, and the third example compares a column to the value returned by a subquery.

We can also build conditions that use the inequality comparison operator "!=". In a previous example, we used the NOT operator to find information about parts supplied by every supplier other than Acme Industries and Tilton Enterprises. Using the != operator rather than using NOT makes the query easier to understand and removes the need for the OR operator: 

SELECT p.part_nbr, p.name, p.supplier_id, p.status, p.inventory_qty,
  s.supplier_id, s.name
FROM part p, supplier s
WHERE s.supplier_id = p.supplier_id
  AND s.name != 'Acme Industries'
  AND s.name != 'Tilton Enterprises';

While this is an improvement over the previous version, the next section shows an even cleaner way to represent the same logic.

2.4.2 Membership Conditions

Along with determining whether two expressions are identical, it is often useful to determine whether one expression can be found within a set of expressions. Using the IN operator, you can build conditions that will evaluate to TRUE if a given expression exists in a set of expressions: 

s.name IN ('Acme Industries', 'Tilton Enterprises')

You may also add the NOT operator to determine whether an expression does not exist in a set of expressions: 

s.name NOT IN ('Acme Industries', 'Tilton Enterprises')

Most people prefer to use a single condition with IN or NOT IN instead of writing multiple conditions using = or !=, so we will take one last stab at our Acme/Tilton query: 

SELECT p.part_nbr, p.name, p.supplier_id, p.status, p.inventory_qty,
  s.supplier_id, s.name
FROM part p, supplier s
WHERE s.supplier_id = p.supplier_id
  AND s.name NOT IN ('Acme Industries', 'Tilton Enterprises');

Along with prefabricated sets of expressions, subqueries may be employed to generate sets on the fly. If a subquery returns exactly one row, you may use a comparison operator; if a subquery returns more than one row, or if you're not sure whether the subquery might return more than one row, use the IN operator. The following example updates all orders that contain parts supplied by Eastern Importers: 

UPDATE cust_order
SET sale_price = sale_price *1.1
WHERE cancelled_dt IS NULL
  AND ship_dt IS NULL
  AND order_nbr IN
 (SELECT li.order_nbr
  FROM line_item li,part p, supplier s
  WHERE s.name = 'Eastern Importers'
    AND s.supplier_id = p.supplier_id
    AND p.part_nbr = li.part_nbr);

The subquery evaluates to a (potentially empty) set of order numbers. All orders whose order number exists in that set are then modified by the UPDATE statement.

2.4.3 Range Conditions

If you are dealing with dates or numeric data, you may be interested in whether a value falls within a specified range rather than whether it matches a specific value or exists in a finite set. For such cases, you may use the BETWEEN... AND operator, as in: 

DELETE FROM cust_order
WHERE order_dt BETWEEN '01-JUL-2001' AND '31-JUL-2001';

To determine whether a value lies outside a specific range, you can add the NOT operator: 

SELECT order_nbr, cust_nbr, sale_price
FROM cust_order
WHERE sale_price NOT BETWEEN 1000 AND 10000;

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.

Ranges may also be specified using the operators <, >, <=, and >=, although doing so requires writing two conditions rather than one. The previous query could also be expressed as: 

SELECT order_nbr, cust_nbr, sale_price
FROM cust_order
WHERE sale_price < 1000 OR sale_price > 10000;

2.4.4 Matching Conditions

When dealing with character data, there are some situations where you are looking for an exact string match, and others where a partial match is sufficient. For the latter case, you can use the LIKE operator along with one or more pattern-matching characters, as in: 

DELETE FROM part
WHERE part_nbr LIKE 'ABC%';

The pattern-matching character "%" matches strings of any length, so all of the following part numbers would be deleted: 'ABC', 'ABC-123', 'ABC9999999'. If you need finer control, you can use the underscore ( _ ) pattern-matching character to match single characters, as in: 

DELETE FROM part
WHERE part_nbr LIKE '_B_';

For this pattern, any part number with exactly 3 characters with a B in the middle would be deleted. Both pattern-matching characters may be utilized in numerous combinations to find the desired data. Additionally, the NOT operator may be employed to find strings that don't match a specified pattern. The following example deletes all parts whose name does not contain a Z in the third position followed later by the string "T1J": 

DELETE FROM part
WHERE part_nbr NOT LIKE '_  _Z%T1J%';

Oracle provides a slew of built-in functions for handling character data that can be used to build matching conditions. For example, the condition part_nbr LIKE 'ABC%' could be rewritten using the SUBSTR function as SUBSTR(part_nbr, 1, 3) = 'ABC'. For definitions and examples for all of Oracle's built-in functions, see Oracle SQL: The Essential Reference (O'Reilly).

2.4.5 Handling NULL

The NULL expression represents the absence of a value. If, when entering an order into the database, you are uncertain when the order will be shipped, it is better to leave the ship date undefined than to fabricate a value. Until the ship date has been determined, therefore, it is best to leave the ship_dt column NULL. NULL is also useful for cases where data is not applicable. For example, a cancelled order's shipping date is no longer applicable and should be set to NULL.

When working with NULL, the concept of equality does not apply; a column may be NULL, but it will never equal NULL. Therefore, you will need to use the special operator IS when looking for NULL data, as in: 

UPDATE cust_order
SET expected_ship_dt = SYSDATE + 1
WHERE ship_dt IS NULL;

In this example, all orders whose shipping date hasn't been specified will have their expected shipping date bumped forward by one day.

You may also use the NOT operator to locate non-NULL data:

UPDATE cust_order
SET expected_ship_dt = NULL
WHERE ship_dt IS NOT NULL;

This example sets the expected shipping date to NULL for all orders that have already shipped. Notice that the SET clause uses the equality operator (=) with NULL, whereas the WHERE clause uses the IS and NOT operators. The equality operator is used to set a column to NULL, whereas the IS operator is used to evaluate whether a column is NULL. A great many mistakes might have been avoided had the designers of SQL chosen a special operator to be utilized when setting a column to NULL (i.e., SET expected_ship_dt TO NULL), but this is not the case. To make matters worse, 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;

Hopefully, you would quickly recognize that the previous query never returns data and replace the equality operator with IS. However, there is a more subtle mistake involving NULL that is harder to spot. Say you are looking for all employees who are not managed by Jeff Blake, whose employee ID is 11. Your first instinct may be to run the following query: 

SELECT fname, lname, manager_emp_id
FROM employee
WHERE manager_emp_id != 11;

FNAME                LNAME                MANAGER_EMP_ID
-------------------- -------------------- --------------
Alex                 Fox                              28
Chris                Anderson                         28
Lynn                 Nichols                          28
Eric                 Iverson                          28
Laura                Peters                           28
Mark                 Russell                          28

While this query returns rows, it leaves out those employees who are top-level managers and, thus, are not managed by anyone. Since NULL is neither equal to 11 nor not equal to 11, this set of employees is absent from the result set. In order to ensure that all employees are considered, you will need to explicitly handle NULL, as in: 

SELECT fname, lname, manager_emp_id
FROM employee
WHERE manager_emp_id IS NULL OR manager_emp_id != 11;

FNAME                LNAME                MANAGER_EMP_ID
-------------------- -------------------- --------------
Bob                  Brown
John                 Smith
Jeff                 Blake
Alex                 Fox                              28
Chris                Anderson                         28
Lynn                 Nichols                          28
Eric                 Iverson                          28
Laura                Peters                           28
Mark                 Russell                          28

Including two conditions for every nullable column in your WHERE clause can get a bit tiresome. Instead, you can use Oracle's built-in function NVL, which 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;

FNAME                LNAME                MANAGER_EMP_ID
-------------------- -------------------- --------------
Bob                  Brown
John                 Smith
Jeff                 Blake
Alex                 Fox                              28
Chris                Anderson                         28
Lynn                 Nichols                          28
Eric                 Iverson                          28
Laura                Peters                           28
Mark                 Russell                          28

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.

    Team LiB   Previous Section   Next Section