Oracle SQL Operators and Conditions

SQL Conditions

A condition is a phrase within the WHERE clause that determines whether a row is chosen. A condition is made up of a pair of expressions with a comparison operator between them. The following MouseOver shows a few examples of operators and conditions.

1. LIKE Operator: The LIKE operator, when combined with two wildcards, allows you to compare a value with a partial value. For example, let us say that you know your customer's last name begins with L, but you cannot recall how the rest of the name is spelled. To list all customers whose last names begin with the letter L, you would use this query:
   The results look like this:
   

   

2. Two Wildcards: The two wildcards^[1] available are:
   • %. The percent sign can be used to represent any number of characters.
   • _. The underscore represents a single character.
   
   You can use both wildcards within a single expression if you wish. In fact, you can use either wildcard more than one time within the same expression. You can concatenate them to a column, so that the expression evaluated contains data from a column. For example, imagine that you want to know which of your customers have the same first letter in the first and last names (such as Lois Lane), the query would be:
   

   SELECT FIRSTNAME, LASTNAME
   FROM CUSTOMER
   WHERE LASTNAME LIKE SUBSTR(FIRSTNAME,1,1) || '%'
   

   
   Notice that the WHERE clause uses the substring function , SUBSTR, to strip off the first letter of the first name. Then the literal, ‘%’ was concatenated (||) to this expression. If the person's first name was Sammy, the expression in the where clause would become ‘S%’. The following graphic shows the results of the query.
   

   

3. IS NULL: The IS NULL comparison allows you to determine whether an expression contains a null value. For example, you can find all the rows that contain null values in the PACKAGE_ID column by using this query:
   

   PACKAGE_ID IS NULL
   

   
   You can also use IS NULL to compare (indirectly) two expressions that are both null. Look at a simple phrase such as:
   

   P.LAST_UPDATE_DATE = PCL.LAST_UPDATE_DATETIME
   

   
   If both values happen to be null values, the comparison is false. What if you want to include rows where both values are null? The following set of comparisons does the trick:
   

   (P.LAST_UPDATE_DATE  = PCL.LAST_UPDATE_DATETIME
    OR
   (P.LAST_UPDATE_DATE IS NULL 
     AND 
     PCL.LAST_UPDATE_DATETIME IS NULL))
   

   
   Enclose the entire set of comparisons in parentheses, to ensure correct sequence of evaluation. You can use IS NOT NULL to determine that an expression does not contain a null value. For example, to check the LAST_UPDATE_DATE use this code:
   

   P.LAST_UPDATE_DATE IS NOT NULL
   

4. NOT Operator: Reverse the logic of any comparison or group of comparisons by adding NOT and enclosing the comparison in parentheses.
   You can place NOT at the beginning of any simple comparison:
   

   NOT TOTAL_ITEM_AMOUNT > 30 
   

5. Oracle Exclusive: Within Oracle, you can use parentheses to group more than one comparison together, place NOT in front of the whole thing, and reverse the resulting logic. For example, here is a comparison that finds rows where total sales are less than $45 and the subtotal of items is over $30.
   

   TOTAL_ITEM_AMOUNT > 30 AND TOTAL_SALE_AMOUNT < 45
   

   
   If you want to see sales that do not fit these criteria, add the NOT and place all in parentheses:
   

   NOT ( TOTAL_ITEM_AMOUNT > 30 AND TOTAL_SALE_AMOUNT 
   < 45 )